JDBC knowledge learning -- using PreparedStatement and ResultSetMetaData

Summary of knowledge points

Use PreparedStatement:
String sql="INSERT INTO USER VALUES(?,?,?,?,?,?) ";id,name,pwd,sex,home,info
1. Create PreparedStatement
PreparedStatement ps=conn.PreparedStatement(sql);
2. Call setXXX() method to set the value of placeholder
3. Execute sql statement
Maximize performance to prevent sql injection problems.
For example: select * from user where username = 'a' OR
password = 'AND password = ' OR '1'='1';
ResultSetMetaData: describes how many columns the ResultSet metadata object can get from the ResultSet. What is the column name? Call getMetaData() method of ResultSet.
Code example:

    @Test
    public void test(){
        Connection con = null;
        PreparedStatement statement = null;
        try {
            //Get database connection
            con = StatementTest.getConnection();
            //sql statement to insert
            String sql = "insert into user(name,pwd,sex,home,info)" +
                    "values(?,?,?,?,?);";
            //Perform insert operation
            //1. Get the Statement object to execute the sql Statement
            statement = con.prepareStatement(sql);
            statement.setString(1,"Willim");
            statement.setString(2,"123456");
            statement.setString(3,"male");
            statement.setString(4,"beijing");
            statement.setString(5,"CHA");
            //2 call method to insert
            statement.executeUpdate(    );
            System.out.println("sql Successful implementation");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseSource(statement, con);
        }
        }
    public  Connection getConnection() {
        //String to connect to the database
        String driverClass = null;//Full class name of driver
        String jdbcUrl = null;
        String user = null;
        String password = null;
        Connection connection1 = null;
        try {
            //Read the jdbc.properties file under the classpath
            InputStream in =
                    this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(in);
            driverClass = properties.getProperty("driver");
            jdbcUrl = properties.getProperty("jdbcUrl");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            //Load database driver
            Class.forName(driverClass);
            //Get database connection
            connection1 = DriverManager.getConnection(jdbcUrl, user, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection1;
    }

Use ResultSetMetaData object

Code example:

public class ResultSetData {
    @Test
    public void testResultSet() throws Exception{
        //Get the data of the specified id and print it
        // Get connection
        Connection connection=getConnection();
        //Preparing for sql
        String sql="select id User_Id,name User_Name,pwd Password ,home Home from user where id=8";
        //Get statement
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //Execution query
        ResultSet re = preparedStatement.executeQuery();
       //Put it in Map.
        Map<String ,Object> values=new HashMap<String ,Object>();
        //Get ResultSetMetaData object
        ResultSetMetaData rsmd=re.getMetaData();
        //Print column names for each column
        while(re.next()) {
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                String columnLabel = rsmd.getColumnLabel(i + 1);
                Object column=re.getObject(columnLabel);
                values.put(columnLabel,column);
            }
        }
        System.out.println(values);
       //create object
       Class clazz=User.class;
       Object object=clazz.newInstance();
       for(Map.Entry<String,Object> entry:values.entrySet()){
           String filedName=entry.getKey();
           Object filedValues=entry.getValue();
           System.out.print(filedName+"\t"+filedValues+"\t");
       }
        //6. Close database resources
        re.close();
        JDBCTools.releaseSource(preparedStatement,connection);
    }
}

Published 26 original articles, won praise 1, visited 317
Private letter follow

Tags: SQL Database JDBC

Posted on Fri, 14 Feb 2020 10:50:26 -0500 by sriusa