JDBC知识学习——使用PreparedStatement及ResultSetMetaData

知识点汇总

使用PreparedStatement：
String sql="INSERT INTO USER VALUES(?,?,?,?,?,?) ";id,name,pwd,sex,home,info
1.创建PreparedStatement
PreparedStatement ps=conn.PreparedStatement(sql);
2.调用setXXX()方法设置占位符的值
3.执行sql语句
最大可能提高性能，可防止sql注入问题。
如：select * from user where username = ‘a’ OR
password = 'AND password = ’ OR ‘1’=‘1’;
ResultSetMetaData：描述ResultSet的元数据对象从中可以获取到结果集中有多少列。列名是什么？ 调用ResultSet的getMetaData()方法。
代码示例：

    @Test
    public void test(){
        Connection con = null;
        PreparedStatement statement = null;
        try {
            //获取数据库连接
            con = StatementTest.getConnection();
            //准备插入的sql语句
            String sql = "insert into user(name,pwd,sex,home,info)" +
                    "values(?,?,?,?,?);";
            //执行插入操作
            //1.获取执行sql语句的Statement对象
            statement = con.prepareStatement(sql);
            statement.setString(1,"Willim");
            statement.setString(2,"123456");
            statement.setString(3,"男");
            statement.setString(4,"beijing");
            statement.setString(5,"CHA");
            //2调用方法进行插入
            statement.executeUpdate(    );
            System.out.println("sql执行成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseSource(statement, con);
        }
        }
    public  Connection getConnection() {
        //连接数据库的字符串
        String driverClass = null;//驱动的全类名
        String jdbcUrl = null;
        String user = null;
        String password = null;
        Connection connection1 = null;
        try {
            //读取类路径下的jdbc.properties文件
            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");
            //加载数据库驱动程序
            Class.forName(driverClass);
            //获取数据库连接
            connection1 = DriverManager.getConnection(jdbcUrl, user, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection1;
    }

使用ResultSetMetaData对象

代码示例：

public class ResultSetData {
    @Test
    public void testResultSet() throws Exception{
        //获取指定id的数据并打印
        // 获取连接
        Connection connection=getConnection();
        //准备sql
        String sql="select id User_Id,name User_Name,pwd Password ,home Home from user where id=8";
        //获取statement
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //执行查询
        ResultSet re = preparedStatement.executeQuery();
       //放到Map中
        Map<String ,Object> values=new HashMap<String ,Object>();
        //得到ResultSetMetaData对象
        ResultSetMetaData rsmd=re.getMetaData();
        //打印每一列的列名
        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);
       //创建对象
       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.关闭数据库资源
        re.close();
        JDBCTools.releaseSource(preparedStatement,connection);
    }
}