JDBC(3)：实现DDL、DML、DQL和代码优化

JDBC实现DDL

DDL(Data Definition Language): 数据定义语言, 用于定义/修改/删除数据对象(如表)的数据结构。

 @Test    public void testDDL() {        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://localhost:3306/address";        String user = "root";        String password = "password";        Connection conn = null;        Statement stmt = null;        try {            // 1.驱动程序的注册            Class.forName(driver);            // 2.获取连接            conn = (Connection) DriverManager.getConnection(url,user,password);            // 3.创建statment            stmt = (Statement) conn.createStatement();            // 4.准备sql            String sql = "create table user_info( id int primary key not null  AUTO_INCREMENT, username VARCHAR (20) , password VARCHAR (20) )AUTO_INCREMENT =1;";            // 5.执行sql语句，得到返回结果            int count = stmt.executeUpdate(sql);            // 6、获取返回结果            System.out.println("本次执行共影响了：" + count + "行数据");        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException(e);        } finally {             // 7.关闭连接资源（注意顺序：后打开的先关闭）            if (stmt != null) {                try {                    stmt.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }        }           }

运行结果(成功生成表)：

本次执行共影响了：0行数据

JDBC实现DML

DML(Data Manipulation Language): 数据操作语言，用于添加/修改/查询数据库中数据,操作数据库对象中包含的数据，也就是说操作的单位是记录。

insert

   @Test   public void testInsert() {        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://127.0.0.1:3306/address";        String user = "root";        String password = "password";        Connection conn = null;        Statement stmt = null;        try {            // 1.驱动程序的注册            Class.forName(driver);            // 2.获取连接            conn = (Connection) DriverManager.getConnection(url,user,password);            // 3.创建statment            stmt = (Statement) conn.createStatement();            // 4.准备sql            String sql = "INSERT INTO user_info (username, password) values ('user1','pwd1')";            // 5.执行sql语句，得到返回结果            int count = stmt.executeUpdate(sql);            // 6、获取返回结果            System.out.println("本次执行共影响了：" + count + "行数据");        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException(e);        } finally {             // 7.关闭连接资源（注意顺序：后打开的先关闭）            if (stmt != null) {                try {                    stmt.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }        }           }

delete

@Test   public void testDelete() {        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://127.0.0.1:3306/address";        String user = "root";        String password = "password";        Connection conn = null;        Statement stmt = null;        try {            // 1.驱动程序的注册            Class.forName(driver);            // 2.获取连接            conn = (Connection) DriverManager.getConnection(url,user,password);            // 3.创建statment            stmt = (Statement) conn.createStatement();            // 4.准备sql            String sql = " delete from user_info where  id =1 ";            // 5.执行sql语句，得到返回结果            int count = stmt.executeUpdate(sql);            // 6、获取返回结果            System.out.println("本次执行共影响了：" + count + "行数据");        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException(e);        } finally {             // 7.关闭连接资源（注意顺序：后打开的先关闭）            if (stmt != null) {                try {                    stmt.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }        }           }

update

  @Test   public void testUpdate() {        String driver = "com.mysql.jdbc.Driver";        String url = "jdbc:mysql://127.0.0.1:3306/address";        String user = "root";        String password = "password";        Connection conn = null;        Statement stmt = null;        try {            // 1.驱动程序的注册            Class.forName(driver);            // 2.获取连接            conn = (Connection) DriverManager.getConnection(url,user,password);            // 3.创建statment            stmt = (Statement) conn.createStatement();            // 4.准备sql            String sql = "UPDATE user_info  SET username = 'iamupdate'  where id =1 ";            // 5.执行sql语句，得到返回结果            int count = stmt.executeUpdate(sql);            // 6、获取返回结果            System.out.println("本次执行共影响了：" + count + "行数据");        } catch (Exception e) {            e.printStackTrace();            throw new RuntimeException(e);        } finally {             // 7.关闭连接资源（注意顺序：后打开的先关闭）            if (stmt != null) {                try {                    stmt.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }        }           }

运行结果：

本次执行共影响了：1行数据

JDBC实现DQL

DQL:数据查询语言。

@Test  public void testQuery() {    String driver = "com.mysql.jdbc.Driver";    String url = "jdbc:mysql://127.0.0.1:3306/address";    String user = "root";    String password = "password";    Connection conn = null;    Statement stmt = null;    try {          // 1.驱动程序的注册           Class.forName(driver);           // 2.获取连接           conn = (Connection) DriverManager.getConnection(url,user,password);           // 3.创建statment           stmt = (Statement) conn.createStatement();        String sql = " select * from user_info ";        ResultSet resultSet = stmt.executeQuery(sql);        //ResultSet 对象具有指向其当前数据行的光标。 最初，光标被置于第一行之前。调用 next() 方法将光标移动到下一行；        //因为该方法在 ResultSet 对象没有下一行时返回 false        //读取方法1 – 通过字段名称来读取        while(resultSet.next()){            int id = resultSet.getInt("ID"); //不区分大小写            String name = resultSet.getString("username");            String passwd = resultSet.getString("password");            System.out.println("id:"+id+" 用户名："+name+" 密码："+passwd);        }        System.out.println("-------------------------------------------------");        resultSet = stmt.executeQuery(sql);        //读取方法2 – 通过索引来遍历读取        while(resultSet.next()){            int id = resultSet.getInt(1);            String name = resultSet.getString(2);            String passwd = resultSet.getString(3);            System.out.println("id:"+id+" 用户名："+name+" 密码："+passwd);        }    }  catch (Exception e) {           e.printStackTrace();           throw new RuntimeException(e);       } finally {         // 7.关闭连接资源（注意顺序：后打开的先关闭）           if (stmt != null) {               try {                   stmt.close();               } catch (SQLException e) {                   e.printStackTrace();                   throw new RuntimeException(e);               }           }           if (conn != null) {               try {                   conn.close();               } catch (SQLException e) {                   e.printStackTrace();                   throw new RuntimeException(e);               }           }    }      }

执行结果：

id:1 用户名：user1 密码：pwd1id:2 用户名：user2 密码：pwd2id:3 用户名：user3 密码：pwd3-------------------------------------------------id:1 用户名：user1 密码：pwd1id:2 用户名：user2 密码：pwd2id:3 用户名：user3 密码：pwd3

代码优化

以上代码重复率很高，看起来臃肿，简化一下，抽取出共同部分写个工具类，代码会看起来整洁易读一些，使用起来也会方便很多。另外，前文也说过，Statement不安全，相对起来，PrepareStatement比较安全，有预编译，执行量大的话更快。
SqlHelper.java

package myjdbc.demo;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;public class SqlHelper {    private static String driver = "com.mysql.jdbc.Driver";    private static String url = "jdbc:mysql://127.0.0.1:3306/address";    private static String user = "root";    private static String password = "password";    private static Connection connection = null;    private static PreparedStatement preparedStatement = null;    private static ResultSet resultSet = null;    static{        //加载驱动        try {            Class.forName(driver);        } catch (ClassNotFoundException e) {            e.printStackTrace();            System.out.println("jdbc驱动程序注册失败！");  //正式代码用log输出        }    }    /**     * 方法名：getConnection     * 详述：获取连接对象的方法     * 开发人员：NowUSeeMe     * @return Connection     */    public static Connection getConnection() {        try {            return (Connection) DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            e.printStackTrace();            throw new RuntimeException(e);        }    }    /**     * 方法名：close     * 详述：释放资源     * 开发人员：NowUSeeMe     * @param rs     * @param conn     * @param stmt void     */    public static void close(ResultSet rs, Connection conn, Statement stmt){         //关闭资源(先开后关)        if(null != rs)        {            try{                rs.close();            }            catch(SQLException e){                e.printStackTrace();            }            rs=null;        }        if(null != stmt){            try {                stmt.close();            } catch (SQLException e) {                e.printStackTrace();                throw new RuntimeException(e);            }        }        if(null != conn){            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();                throw new RuntimeException(e);            }        }    }    /**     * 方法名：executeQuery     * 详述：查询     * 开发人员：NowUSeeMe     * @param sql     * @param parameters     * @return ResultSet     */    public static ResultSet executeQuery(String sql,String[] parameters)    {        try        {            connection = getConnection();                       preparedStatement = (PreparedStatement) connection.prepareStatement(sql);            if(null != parameters){                for(int i=0;i<parameters.length;i++)                {                    preparedStatement.setString(i+1,parameters[i]);                }            }            resultSet = preparedStatement.executeQuery();        }catch(Exception e) {            e.printStackTrace();            throw new RuntimeException(e.getMessage());        }finally{        }        return resultSet;    }    /**     * 方法名：executeUpdate     * 详述：实现增删改     * 开发人员：NowUSeeMe     * 创建时间：2017-2-11     * @param sql     * @param parameters void     */    public static Integer executeUpdate(String sql,String[] parameters){        Integer temp = 0;        try {             connection = getConnection();             preparedStatement = (PreparedStatement) connection.prepareStatement(sql);             if(null != parameters){                 for(int i=0;i<parameters.length;i++)                 {                    preparedStatement.setString(i+1,parameters[i]);                 }             }            temp = preparedStatement.executeUpdate();        } catch (SQLException e) {             e.printStackTrace();        }finally{             close(resultSet, connection, preparedStatement);        }        return temp;    }}

测试查询代码：

    @Test    public void testQuery() throws SQLException {        String sql = " select * from user_info ";        ResultSet resultSet = SqlHelper.executeQuery(sql, null);         while(resultSet.next()){                int id = resultSet.getInt("ID"); //不区分大小写                String name = resultSet.getString("username");                String passwd = resultSet.getString("password");                System.out.println("id:"+id+" 用户名："+name+" 密码："+passwd);        }        System.out.println("----------------------------------");        String sql2 =  " select * from user_info  where id = ? ";        String[] params = {"1"};        resultSet = SqlHelper.executeQuery(sql2, params);        while (resultSet.next()) {            int id = resultSet.getInt(1);            String name = resultSet.getString(2);            String passwd = resultSet.getString(3);            System.out.println("id:"+id+" 用户名："+name+" 密码："+passwd);        }    }

运行结果：

id:1 用户名：user1 密码：pwd1id:3 用户名：user3 密码：pwd3id:2 用户名：user2 密码：pwd2----------------------------------id:1 用户名：user1 密码：pwd1

测试删除代码：

 @Test  public void testDelete() {    String sql = " delete from user_info where  id =1 ";    Integer influentRows = SqlHelper.executeUpdate(sql, null);    System.out.println("InfluentRows: "+influentRows);  }

运行结果：

InfluentRows: 0