Java JDBC学习笔记

JDBC是一个Java应用程序接口，作用是封装了对数据库的各种操作。JDBC由类和接口组成，使用Java开发数据库应用都需要4个主要的接口：Driver、Connection、Statement、ResultSet，这些接口定义了使用SQL访问数据库的一般架构。

JDBC七步走

1. 加载驱动

Class.forName(driverName);

2. 指定用户名和密码

Connection conn = DriverManager.getConnection(url,user,pwd);

3. 获取连接

if (!conn.isClosed()) {
			System.out.println("###Connection successful!###");
		}

4. 创建一个Statement对象

Statement stmt = conn.createStatement();

5. 执行sql语句

ResultSet rs = stmt.executeQuery(sql_query);

6. 展示结果集

while (rs.next()) {
				System.out.println(rs.getString("id") + " | " + rs.getString("name"));
			}

• 关闭对象

rs.close();
	stmt.close();
	conn.close();

重写Dao工具类

• 通过Dao更快捷的的操作数据库

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @desc 利用JDBC重构获取一个Dao工具类
 * @author 86176
 * @time 2021-05-23
 */
public class Dao {
	// -------连接四要素
	private String driverName = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://127.0.0.1:3307/test20210523_51?useUnicode=true&characterEncoding=utf-8";
	private String user = "root";
	private String pwd = "root";

	/**
	 * @desc 1.获取连接
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConnection() throws ClassNotFoundException, SQLException {
		// 加载驱动
		Class.forName(driverName);

		// 指定URL/user+pwd
		Connection conn = DriverManager.getConnection(url, user, pwd);
		return conn;
	}

	/**
	 * @desc 2.释放连接
	 * @param conn
	 * @param stmt
	 * @param rs
	 * @throws SQLException
	 */
	public void releaseConnection(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
		System.out.println("###关闭连接:releaseConnection");
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
		if (conn != null) {
			conn.close();
		}

	}
	private void releaseConnection(Connection conn, Statement stmt) throws SQLException {
		if (stmt != null) {
			stmt.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * @desc 3.查询全部
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public List<Map<String, Object>> executeQueryForList(String sql) throws ClassNotFoundException, SQLException {
		System.out.println("查询全部--sql--" + sql);
		Connection conn = this.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		List<Map<String, Object>> list = rsToList(rs);
		this.releaseConnection(conn, stmt, rs);
		return list;
	}

	/**
	 * @desc 4.将rs结果集转变为List<map>
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private List<Map<String, Object>> rsToList(ResultSet rs) throws SQLException {
		List<Map<String, Object>> rowsList = new ArrayList<Map<String, Object>>();
		// System.out.println(rs.getMetaData().getColumnCount()+"rows");
		while (rs.next()) // 控制循环行
		{
			Map<String, Object> colsMap = new HashMap<String, Object>();
			for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) // 内部循环控制列
			{
				switch (rs.getMetaData().getColumnType(i)) {
				case Types.VARCHAR:
					colsMap.put(rs.getMetaData().getColumnName(i), rs.getString(i));
					break;
				case Types.INTEGER:
					colsMap.put(rs.getMetaData().getColumnName(i), rs.getInt(i));
					break;
				case Types.BLOB: // 图片类型
					InputStream in = rs.getBinaryStream(i);
					colsMap.put(rs.getMetaData().getSchemaName(i), in);
					break;
				default:
					colsMap.put(rs.getMetaData().getColumnName(i), rs.getString(i));
					break;
				}
			}
			rowsList.add(colsMap);
		}
		return rowsList;
	}

	/**
	 * @desc 5.查询一条
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public Map<String, Object> executeQueryForMap(String sql) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		System.out.println("查询一条--sql--" + sql);
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			List<Map<String, Object>> list = this.rsToList(rs);
			if (!list.isEmpty()) {
				return list.get(0);
			}
		} finally {
			this.releaseConnection(conn, stmt, rs);
		}
		return null;
	}

	/**
	 * @desc 6.查询一共有多少条
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int executeQueryForCount(String sql) throws ClassNotFoundException, SQLException {
		System.out.println("查询一共有多少条的sql:" + sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} finally {
			this.releaseConnection(conn, stmt, rs);
		}

		return 0;
	}
	/**
	 * 7.执行增删改操作
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
		System.out.println("增删改:" + sql);
		Connection conn = this.getConnection();
		Statement stmt = conn.createStatement();
		int count = stmt.executeUpdate(sql);
		this.releaseConnection(conn, stmt);
		
		return count;
	}


	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Dao dao = new Dao();
		/*模糊搜索 select*from user where id like '%3'
		 *查询全部 select*from user
		 *查询个数 select count(*) from user
		 *select*from user where id not in (004,003)
		 */
	System.out.println(dao.executeQueryForList("select*from user where id not in (004,003)"));
//	System.out.println(dao.executeQueryForMap("select*from user where id='003'"));
//		System.out.println(dao.executeQueryForCount("select count(*) from user"));
	}

}