JDBC知识学习——Statement执行更新和ResultSet查询操作

Statement用于执行sql语句的对象：

1.通过Connection的createStatement()方法来获取
2.通过executeUpdate(sql)执行sql语句，可以执行insert，update，delete
代码示例：

public class StatementTest {
    /*
    * ResultSet结果集封装了使用JDBC查询得到的结果
    * 1.调用Statement对象的executeQuery(sql)得到结果集
    * 2.ResultSet返回的是一张数据表
    * */
    @Test
    public void testResultSet() throws Exception{
        //获取指定id的数据并打印
        //1.获取连接
        Connection connection=getConnection();
        //2.获取statement
        Statement statement=connection.createStatement();
        //3.准备sql
        String sql="select id,name,pwd,sex,home,info from user where id=8";
        String sql1="select id,name,pwd,sex,home,info from user " ;//查询多条数据
        //4.执行查询
        ResultSet re = statement.executeQuery(sql1);
        //5.处理得到的ResultSet
    while(re.next()){//if(re.next()){
        int id=re.getInt(1);
        String name=re.getString("name");
        String sex=re.getString(4);
        String info=re.getString(5);
        System.out.println(id+"\t"+name+"\t"+sex+"\t"+info);
    }
        //6.关闭数据库资源
        re.close();
        JDBCTools.releaseSource(statement,connection);
    }

    @Test
    public void test1() {
        Connection con = null;
        Statement statement = null;
        try {
            //获取数据库连接
            con = getConnection();
            //准备插入的sql语句
            String sql = "insert into user(name,pwd,sex,home,info)" +
                    "values('Roy','111','Man','Lz','sky');";
            String sql1 = "delete from user where id=9;";
            String sql2 = "update user set name='Jerry' where id=6;";

            //执行插入操作
            //1.获取执行sql语句的Statement对象
            statement = con.createStatement();
            //2调用方法进行插入
            statement.executeUpdate(sql2);
            System.out.println("sql执行成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseSource(statement,con);
            /*if (statement != null)
            //关闭Statement对象
            {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null)
            //关闭连接
            {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }*/

        }
    }
    /**
    *@ClassName StatementTest
    *@Description 获取数据库资源连接
    *@Param []
    *@Return java.sql.Connection
    *@Date 2020/2/13 15:57
    *@Author Roy
    */
    public Connection getConnection() throws Exception {
        //连接数据库的字符串
        String driverClass = null;//驱动的全类名
        String jdbcUrl = null;
        String user = null;
        String password = null;
        //读取类路径下的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);
        //获取数据库连接
        Connection connection1 = DriverManager.getConnection(jdbcUrl, user, password);
        return connection1;
    }
}

操作JDBC的工具类封装了工具方法

代码示例：

public class JDBCTools {
    /**
    *@ClassName JDBCTools
    *@Description 读取连接的方法通过读取配置文件获取数据库服务器连接
    *@Param []
    *@Return java.sql.Connection
    *@Date 2020/2/13 15:51
    *@Author Roy
    */
    public static Connection getConnection() throws  Exception{
        String driverClass=null;
        String jdbcUrl=null;
        String user=null;
        String password=null;
        //读取类路径下的jdbc.properties文件
        InputStream in=
                JDBCTools.class.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");
        //通过反射创建对象
        Driver driver1=(Driver) Class.forName(driverClass).newInstance();
        Properties inf=new Properties();
        inf.put("user",user);
        inf.put("password",password);
        //获取数据库连接
        Connection connection1=driver1.connect(jdbcUrl,inf);
        return connection1;
    }
    /**
    *@ClassName JDBCTools
    *@Description 释放资源，关闭statement和Connection
    *@Param [statement, connection]
    *@Return void
    *@Date 2020/2/13 15:54
    *@Author Roy
    */

    public static void releaseSource(Statement statement,Connection connection){
        if (statement != null)
        //关闭Statement对象
        {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null)
        //关闭连接
        {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}