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.Driver
url=jdbc:mysql://localhost:3316/test
user=root
password=

通过driver获取connection

//通过driver获取connection
	public 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方法获取connection
		Connection connection = driver.connect(url, info);
		return connection;
	}

通过driverManager获取connection

	//通过driverManager获取connection
	public 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方法获取Connection
		Connection connection = DriverManager.getConnection(url,user,password);
		return connection;
	}

使用PreparedStatement

	//使用PreparedStatement
	public 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 等)返回
	 */
	@Test
	public 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 的集合. 
	 */
	@Test
	public 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 查询的列名(不是列的别名), 值: 列的值. 
	 */
	@Test
	public 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
	 * 对象对应的对象. 
	 */
	@Test
	public 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
	 * 参数对应的对象. 
	 */
	@Test
	public 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{

		@Override
		public 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 方法的返回值
	 * 
	 */
	@Test
	public 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);
		}
	}
	
	@Test
	public 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);
		}


	}