JDBC

最新推荐文章于 2022-03-26 18:46:48 发布

chengyu0726

最新推荐文章于 2022-03-26 18:46:48 发布

阅读量146

点赞数

分类专栏： DB

本文链接：https://blog.csdn.net/chengyu0726/article/details/116377133

版权

这篇博客深入讲解了JDBC，从获取数据库连接开始，探讨了Statement与PreparedStatement的区别，特别是SQL注入问题。重点介绍了PreparedStatement的使用，包括添加、更新、删除和查询操作，以及Blob类型字段的处理。还详细讨论了批量操作、数据库事务的ACID属性、并发问题及隔离级别。此外，介绍了DAO模式和常见的数据库连接池技术，如C3P0、DBCP和Druid，以及Apache-DBUtils库的CRUD操作。

摘要由CSDN通过智能技术生成

1. 获取数据库连接

lib 下添加数据库驱动。
右键【Build Path】⇒【Add to Build Path】
例：mysql-connector-java-8.0.23.jar

    @Test
	public void connectionTest01() throws SQLException {
   
		// 获取Driver 实现类对象
		Driver driver = new com.mysql.jdbc.Driver();
		String url = "jdbc:mysql://localhost:3306/book";
		Properties info = new Properties();
		info.setProperty("user", "root");
		info.setProperty("password", "tiger");
		Connection conn = driver.connect(url, info);
		System.out.println(conn);
	}
	// 程序中没有第三方api（new com.mysql.jdbc.Driver()），使程序具有更好的可移植性
	@Test
	public void connectionTest02() throws Exception {
   
		// 获取Driver 实现类对象，使用反射
		Class clazz = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver)clazz.newInstance();

		String url = "jdbc:mysql://localhost:3306/book";
		Properties info = new Properties();
		info.setProperty("user", "root");
		info.setProperty("password", "tiger");
		Connection conn = driver.connect(url, info);
		System.out.println(conn);
	}
	@Test
	public void connectionTest03() throws Exception {
   
		// 获取Driver 实现类对象，使用反射
		Class clazz = Class.forName("com.mysql.jdbc.Driver");
		Driver driver = (Driver)clazz.newInstance();
		// 注册驱动
		DriverManager.registerDriver(driver);
		// 获取连接
		String url = "jdbc:mysql://localhost:3306/book";
		Connection conn = DriverManager.getConnection(url,"root","tiger");
		System.out.println(conn);
	}
	@Test
	public void connectionTest04() throws Exception {
   
		// 加载驱动，mysql 的Driver 实现类进行了驱动的注册
		Class.forName("com.mysql.jdbc.Driver");
		// 获取连接
		String url = "jdbc:mysql://localhost:3306/book";
		Connection conn = DriverManager.getConnection(url,"root","tiger");
		System.out.println(conn);
	}
	@Test
	public void connectionTest05() throws Exception {
   
		// 读取配置文件
		InputStream inStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		// 从流中加载数据
		Properties properties = new Properties();
		properties.load(inStream);

		String username = properties.getProperty("username");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");
		String driverClassName = properties.getProperty("driverClassName");
		// 加载驱动
		Class.forName(driverClassName);
		// 获取连接
		Connection conn = DriverManager.getConnection(url,username,password);
		System.out.println(conn);
	}

jdbc.properties

username=root
password=tiger
url=jdbc:mysql://localhost:3306/book
driverClassName=com.mysql.jdbc.Driver

2. 操作和访问数据库

2.1 Statement SQL 注入问题

用户名：1’ or
密码： =1 or ‘1’ = '1

String sql = "SELECT USER,PASSWORD FROM USER WHERE USER = '"+user+"'" AND PASSWORD = '"+password+"'";

SELECT USER,PASSWORD FROM USER WHERE USER = '1' AND PASSWORD = '=1 or '1' = '1'

使用PrepareStatement() 可以避免该问题。

2.2 PrepareStatement

PrepareStatement 是Statement 的子接口，预编译SQL 语句。

2.2.1 添加

	// 添加
	@Test
	public void testInsert() {
   
		// 获取连接
		Connection conn = null;
		PreparedStatement ps = null;
		try {
   
			// 读取配置文件
			InputStream inStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
			// 从流中加载数据
			Properties properties = new Properties();
			properties.load(inStream);
			String username = properties.getProperty("username");
			String password = properties.getProperty("password");
			String url = properties.getProperty("url");
			String driverClassName = properties.getProperty("driverClassName");
			// 加载驱动
			Class.forName(driverClassName);
			// 获取连接
			conn = DriverManager.getConnection(url,username,password);
			// 预编译SQL语句
			String sql = "INSERT INTO t_user(name,pwd,email) VALUES(?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "chengyu");
			ps.setString(2, "123456");
			ps.setString(3, "111qq.com");

			ps.execute();
		} catch (Exception e) {
   
			e.printStackTrace();
		} finally{
   
			try {
   
				if(ps != null)
				ps.close();
			} catch (SQLException e) {
   
				e.printStackTrace();
			}
			try {
   
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
   
				e.printStackTrace();
			}
		}
	}

2.2.2 更新

创建数据库连接、关闭的工具类

JDBCUtils.java：抽取数据库连接部分工具类，主要负责数据库的连接及关闭操作。

public class JDBCUtils {
   
	public static Connection getConnection() throws Exception {
   
		// 读取配置文件
		InputStream inStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		// 从流中加载数据
		Properties properties = new Properties();
		properties.load(inStream);
		String username = properties.getProperty("username");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");
		String driverClassName = properties.getProperty("driverClassName");
		// 加载驱动
		Class.forName(driverClassName);
		// 获取连接
		Connection conn = DriverManager.getConnection(url,username,password);
		return conn;
	}

	public static void closeResource(Connection conn, Statement ps) {
   
		try {
   
			if(ps != null)
			ps.close();
		} catch (SQLException e) {
   
			e.printStackTrace();
		}
		try {
   
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
   
			e.printStackTrace();
		}
	}
}

更新操作：

	@Test
	public void testUpdate()  {
   
		Connection conn = null;
		PreparedStatement ps = null;
		try {
   
			conn = JDBCUtils.getConnection();
			String sql = "update t_user set name = ? where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "chengwei");
			ps.setString(2, "1006");
			ps.execute();
		} catch (Exception e) {
   
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
   
			JDBCUtils.closeResource(conn, ps);
		}
	}

2.2.3 删除

抽取增删改共通方法：

	public void update(String sql,Object ...obj) {
   
		Connection conn = null;
		PreparedStatement ps = null;
		try {
   
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < obj.length ; i++) {
   
				ps.setObject(i + 1, obj[i]);
			}
			ps.execute();
		} catch (Exception e) {
   
			e.printStackTrace();
		} finally {
   
			JDBCUtils.closeResource(conn, ps);
		}
	}

	@Test
	public void testDelete() {
   
		String sql = "delete from t_user where id = ?";
		update(sql,1007);
	}

2.2.4 查询

2.2.4.1 查询指定表所有列

public class UserForQuery {
   
	@Test
	public void testQuery1() {
   
		Connection conn = null;
		PreparedStatement ps= null;
		ResultSet rs = null;
		try {
   
			conn = JDBCUtils.getConnection();
			String sql = "select * from t_user where id = ? ";
			ps = conn.prepareStatement(sql);
			ps.setObject(1, 1001);
			// 返回结果集
			rs = ps.executeQuery();
			// 处理结果集
			while(rs.next()) {
   // 是否有下一条数据
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String pwd = rs.getString(3);
				String email = rs.getString(4);

				User user = new User(id,name,pwd,email);
				System.out.println(user);
			}
		} catch (Exception e) {
   
			e.printStackTrace();
		} finally {
   
			JDBCUtils.closeResource(conn, ps, rs);
		}
	}
}

JDBCUtils 类中追加ResultSet 的关闭操作：

try {
   
	if(rs != null)
		rs.close();
} catch (SQLException e) {
   
	e.printStackTrace();
}

2.2.4.2 查询指定表任意列

	@Test
    public void testQueryForUser() {
   
    	String sql = "select id,name from  t_user where id = ?";
    	User user = queryForUser( sql,1001 );
    	System.out.println(user);
    }
	// User 表的共同方法
	public User queryForUser(String sql,Object ...obj ) {
   
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
   
			conn = JDBCUtils.getConnection