java基础之 JDBC、DBUtils

JdbcUtils

package com.step.jdbc;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JdbcUtils {//读取数据库连接参数private static String url = null;private static String username = null;private static String password = null;static{Properties prop = new Properties();try {prop.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));} catch (IOException e) {}String driverName = prop.getProperty("jdbc.driverClassName");url = prop.getProperty("jdbc.url");username = prop.getProperty("jdbc.username");password = prop.getProperty("jdbc.password");//加载数据库引擎try {Class.forName(driverName);} catch (ClassNotFoundException e) {}}//获得数据库链接public static Connection getConnection(){Connection conn = null;try {conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {}return conn;}//释放资源public static void release(ResultSet rs, Statement st, Connection conn){if(rs != null){try {rs.close();} catch (Exception e) {}}if(st != null){try {st.close();} catch (Exception e) {}}if(conn != null){try {conn.close();} catch (Exception e) {}}}}

jdbc.properties

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3316/testuser=rootpassword=

通过driver获取connection

//通过driver获取connectionpublic Connection getConnectionByDirver()  throws Exception{//从配置文件中获取链接数据库的信息Properties properties = this.getProperties("jdbc.properties");String driverClass = properties.getProperty("driver");String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");//数据库驱动Driver driver = (Driver)Class.forName(driverClass).newInstance();//数据库连接属性Properties info = new Properties();info.setProperty("user", user);info.setProperty("password", password);//调用driver的connect方法获取connectionConnection connection = driver.connect(url, info);return connection;}

通过driverManager获取connection

//通过driverManager获取connectionpublic Connection getConnectionByDirverManager() throws Exception{//从配置文件中获取链接数据库的信息Properties properties = this.getProperties("jdbc.properties");String driverClass = properties.getProperty("driver");String url = properties.getProperty("url");String user = properties.getProperty("user");String password = properties.getProperty("password");//数据库驱动Class.forName(driverClass);//调用DriverManager的getConnection方法获取ConnectionConnection connection = DriverManager.getConnection(url,user,password);return connection;}

使用PreparedStatement

//使用PreparedStatementpublic void preparedStatementTest(){Map<String,Object> map = new HashMap<String, Object>();String key = "";Object value = "";for(Map.Entry<String, Object> entry : map.entrySet()){key = entry.getKey();value = entry.getValue();}Connection conn = null;PreparedStatement preparedStatement = null;try {//1.获取connection链接conn = this.getConnectionByDirverManager();//2.准备sql语句String sql = "insert into customer(name,age,birth) values(?,?,?)";//3.获取PreparedStatement，此时需传入sql语句preparedStatement = (PreparedStatement) conn.prepareStatement(sql);//4.用set方法给sql语句中的参数赋值,从1开始preparedStatement.setString(1, "hd");preparedStatement.setInt(1, 10);preparedStatement.setDate(1, new Date(new java.util.Date().getTime()));//执行语句preparedStatement.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally{this.release(null, preparedStatement, conn);}}

获取插入值的主键

public void insert() throws SQLException{Connection conn = JdbcUtils.getConnection();String sql = "insert into news(TITLE, AUTHOR) values('AABB','AA0')";PreparedStatement preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);preparedStatement.executeUpdate();//获得插入的主键int keyId;ResultSet rs = preparedStatement.getGeneratedKeys();if(rs.next()){keyId = rs.getInt(1);}JdbcUtils.release(rs, preparedStatement, conn);}

DBUtils：

/** * ScalarHandler: 把结果集转为一个数值(可以是任意基本数据类型和字符串， Date 等)返回 */@Testpublic void testScalarHandler(){Connection connection = null;try {connection = JDBCTools.getConnection();String sql = "SELECT name, email " +"FROM customers";Object result = queryRunner.query(connection, sql, new ScalarHandler());System.out.println(result);} catch (Exception e) {e.printStackTrace();} finally{JDBCTools.releaseDB(null, null, connection);}}/** * MapListHandler: 将结果集转为一个 Map 的 List * Map 对应查询的一条记录: 键: SQL 查询的列名(不是列的别名), 值: 列的值.  * 而 MapListHandler: 返回的多条记录对应的 Map 的集合.  */@Testpublic void testMapListHandler(){Connection connection = null;try {connection = JDBCTools.getConnection();String sql = "SELECT id, name, email, birth " +"FROM customers";List<Map<String, Object>> result = queryRunner.query(connection, sql, new MapListHandler());System.out.println(result);} catch (Exception e) {e.printStackTrace();} finally{JDBCTools.releaseDB(null, null, connection);}}/** * MapHandler: 返回 SQL 对应的第一条记录对应的 Map 对象. * 键: SQL 查询的列名(不是列的别名), 值: 列的值.  */@Testpublic void testMapHandler(){Connection connection = null;try {connection = JDBCTools.getConnection();String sql = "SELECT id, name, email, birth " +"FROM customers";Map<String, Object> result = queryRunner.query(connection, sql, new MapHandler());System.out.println(result);} catch (Exception e) {e.printStackTrace();} finally{JDBCTools.releaseDB(null, null, connection);}}/** * BeanListHandler: 把结果集转为一个 List, 该 List 不为 null, 但可能为 * 空集合(size() 方法返回 0) * 若 SQL 语句的确能够查询到记录, List 中存放创建 BeanListHandler 传入的 Class * 对象对应的对象.  */@Testpublic void testBeanListHandler(){Connection connection = null;try {connection = JDBCTools.getConnection();String sql = "SELECT id, name, email, birth " +"FROM customers";List<Customer> customers = queryRunner.query(connection, sql, new BeanListHandler(Customer.class));System.out.println(customers);} catch (Exception e) {e.printStackTrace();} finally{JDBCTools.releaseDB(null, null, connection);}}/** * BeanHandler: 把结果集的第一条记录转为创建 BeanHandler 对象时传入的 Class * 参数对应的对象.  */@Testpublic void testBeanHanlder(){Connection connection = null;try {connection = JDBCTools.getConnection();String sql = "SELECT id, name customerName, email, birth " +"FROM customers WHERE id >= ?";Customer customer = queryRunner.query(connection, sql, new BeanHandler(Customer.class), 5);System.out.println(customer);} catch (Exception e) {e.printStackTrace();} finally{JDBCTools.releaseDB(null, null, connection);}}QueryRunner queryRunner = new QueryRunner();class MyResultSetHandler implements ResultSetHandler{@Overridepublic Object handle(ResultSet resultSet) throws SQLException {//System.out.println("handle....");//return "atguigu";List<Customer> customers = new ArrayList<>();while(resultSet.next()){Integer id = resultSet.getInt(1);String name = resultSet.getString(2);String email = resultSet.getString(3);Date birth = resultSet.getDate(4);Customer customer = new Customer(id, name, email, birth);customers.add(customer);}return customers;}}/** * QueryRunner 的 query 方法的返回值取决于其 ResultSetHandler 参数的 * handle 方法的返回值 *  */@Testpublic void testQuery(){Connection connection = null;try {connection = JDBCTools.getConnection();String sql = "SELECT id, name, email, birth " +"FROM customers";Object obj = queryRunner.query(connection, sql, new MyResultSetHandler());System.out.println(obj); } catch (Exception e) {e.printStackTrace();} finally{JDBCTools.releaseDB(null, null, connection);}}@Testpublic void testUpdate(){Connection connection = null;try {connection = JDBCTools.getConnection();String sql = "UPDATE customers SET name = ? " +"WHERE id = ?";queryRunner.update(connection, sql, "MIKE", 11);} catch (Exception e) {e.printStackTrace();} finally{JDBCTools.releaseDB(null, null, connection);}}

如何使用 JDBC 调用存储在数据库中的函数或存储过程

public void testCallableStatment() {Connection connection = null;CallableStatement callableStatement = null;try {connection = JDBCTools.getConnection();// 1. 通过 Connection 对象的 prepareCall()// 方法创建一个 CallableStatement 对象的实例.// 在使用 Connection 对象的 preparedCall() 方法时,// 需要传入一个 String 类型的字符串, 该字符串用于指明如何调用存储过程.String sql = "{?= call sum_salary(?, ?)}";callableStatement = connection.prepareCall(sql);// 2. 通过 CallableStatement 对象的 //reisterOutParameter() 方法注册 OUT 参数.callableStatement.registerOutParameter(1, Types.NUMERIC);callableStatement.registerOutParameter(3, Types.NUMERIC);// 3. 通过 CallableStatement 对象的 setXxx() 方法设定 IN 或 IN OUT 参数. 若想将参数默认值设为// null, 可以使用 setNull() 方法.callableStatement.setInt(2, 80);// 4. 通过 CallableStatement 对象的 execute() 方法执行存储过程callableStatement.execute();// 5. 如果所调用的是带返回参数的存储过程, //还需要通过 CallableStatement 对象的 getXxx() 方法获取其返回值.double sumSalary = callableStatement.getDouble(1);long empCount = callableStatement.getLong(3);System.out.println(sumSalary);System.out.println(empCount);} catch (Exception e) {e.printStackTrace();} finally {JDBCTools.releaseDB(null, callableStatement, connection);}}