JDBC笔记

创建数据库连接： 方式一：

public void testConnection1() throws SQLException { //调用mysql驱动程序中Driver Driver driver = new com.mysql.jdbc.Driver(); //统一资源定位 String url = "jdbc:mysql://localhost:3306/db10"; //封装数据库的用户和密码 Properties info = new Properties(); info.setProperty("user" , "root"); info.setProperty("password" , "clearlove7"); Connection connect = driver.connect(url, info); System.out.println(connect); }

方式二：

public void testConnection2() throws Exception { //利用反射，不是某个数据库特有的，可移植性高 Class<?> aClass = Class.forName("com.mysql.jdbc.Driver"); Driver driver = (Driver) aClass.newInstance(); String url = "jdbc:mysql://localhost:3306/db10"; Properties info = new Properties(); info.setProperty("user" , "root"); info.setProperty("password" , "clearlove7"); Connection connect = driver.connect(url, info); System.out.println(connect);

方式三：

public void testConnection3() throws Exception { //这三行可以省略，因为mysql驱动已经自动做了 //加载Driver,这行尽量不省略，因为只有mysql自动加载，其他的不一定 Class<?> aClass = Class.forName("com.mysql.jdbc.Driver"); //Driver driver = (Driver) aClass.newInstance(); //DriverManager代替Driver //DriverManager.registerDriver(driver); String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "clearlove7"; Connection connection = DriverManager.getConnection(url, user, password); System.out.println(connection); }

jdbc.propertise配置文件

url=jdbc:mysql://localhost:3306/test user=root password=clearlove7 driver=com.mysql.jdbc.Driver

方式四：（最终版推荐用）

public void testConnection4() throws Exception { //利用配置文件来达到解耦目的 //加载类加载器来读取配置文件并转为流 InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("connection/jdbc.properties"); Properties pros = new Properties(); pros.load(is); //获取配置文件中的数据 String user = pros.getProperty("user"); String password = pros.getProperty("password"); String url = pros.getProperty("url"); String driver = pros.getProperty("driver"); Class.forName(driver); Connection connection = DriverManager.getConnection(url, user, password); System.out.println(connection); }

操作数据库（增）

package connection; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Properties; public class PreparedStatement { @Test public void testPreparedStatement(){ Connection connection = null; java.sql.PreparedStatement ps = null; try { //加载配置文件 InputStream is = PreparedStatement.class.getClassLoader().getResourceAsStream("connection/jdbc.properties"); Properties pros = new Properties(); pros.load(is); //提取配置文件内容 String user = pros.getProperty("user"); String url = pros.getProperty("url"); String password = pros.getProperty("password"); String driver = pros.getProperty("driver"); //对于mysql可有可无，加载驱动 Class.forName(driver); //获得数据库连接 connection = DriverManager.getConnection(url , user , password); //操作数据库 String sql = "insert into customers(name , email , birth) values(? , ? , ?);"; ps = connection.prepareStatement(sql); ps.setString(1 , "王五"); ps.setString(2 , "wangwu@gmail.com"); //注意：添加Date数据时要new java.sql.Date(date.getTime())； SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date date = sdf.parse("2008-01-01"); ps.setDate(3 , new java.sql.Date(date.getTime())); ps.execute(); } catch (Exception e) { e.printStackTrace(); }finally { try { if(ps != null) { ps.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } try { if(connection != null) { connection.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } } } }

JDBCUnit类

package connection.JDBCUtil; import connection.ConnectionTest; import connection.PreparedStatement; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JDBCUtil { @Test public Connection Connection() throws Exception { InputStream is = ClassLoader.getSystemResourceAsStream("connection/jdbc.properties"); Properties pros = new Properties(); pros.load(is); //提取配置文件内容 String user = pros.getProperty("user"); String url = pros.getProperty("url"); String password = pros.getProperty("password"); String driver = pros.getProperty("driver"); //对于mysql可有可无，加载驱动 Class.forName(driver); //获得数据库连接 Connection connection = DriverManager.getConnection(url, user, password); return connection; } public void Close(Connection connection , Statement state) throws Exception { if(connection != null){ connection.close(); } if(state != null){ state.close(); } } }

操作数据库（改）

public void testUpdate(){ JDBCUtil util = new JDBCUtil(); Connection connection = null; java.sql.PreparedStatement ps = null; try { connection = util.Connection(); String sql = "update customers set name = ? , email = ? , birth = ? where id = ?;"; ps = connection.prepareStatement(sql); ps.setString(1 , "爸爸"); ps.setString(2 , "baba@gmail.com"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date date = sdf.parse("2000-11-04"); ps.setDate(3 , new java.sql.Date(date.getTime())); ps.setInt(4 , 22); ps.execute(); } catch (Exception e) { e.printStackTrace(); }finally { try { util.Close(connection , ps); } catch (Exception e) { e.printStackTrace(); } } } }

数据库公共操作类（增删改）

@Test public void testCommont() throws Exception { String sql = "insert into customers(name , email , birth) values(? , ? , ?);"; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date date = sdf.parse("2020-10-04"); //参数个数对应问号的个数 update(sql , "赵六" , "zhaoliu@gmail.com" , new java.sql.Date(date.getTime())); } public void update(String sql , Object ...arg) throws Exception { JDBCUtil util = new JDBCUtil(); Connection connection = null; java.sql.PreparedStatement ps = null; try { connection = util.Connection(); ps = connection.prepareStatement(sql); for (int i = 0; i < arg.length; i++) { ps.setObject(i + 1 , arg[i]); } ps.execute(); } catch (Exception e) { e.printStackTrace(); }finally { util.Close(connection , ps); } }

数据库操作（查）

@Test public void customersQuery() throws Exception { JDBCUtil util = new JDBCUtil(); Connection connection = null; java.sql.PreparedStatement ps = null; try { connection = util.Connection(); String sql = "select name , email , birth from customers;"; ps = connection.prepareStatement(sql); //结果集对象 ResultSet resultSet = ps.executeQuery(); //对结果集进行输出 while(resultSet.next()){ String name = resultSet.getString(1); String email = resultSet.getString(2); java.sql.Date date = resultSet.getDate(3); System.out.println("name :" + name + "email :" + email + "date :" + date); } } catch (Exception e) { e.printStackTrace(); }finally { util.Close(connection , ps); } }

数据库操作公共操作（查）

@Test public void testQueryCommont() throws Exception { String sql = "SELECT id , NAME , email , birth FROM customers;"; ArrayList<Customer> customers = queryCommont(sql); for (Customer customer : customers) { System.out.println(customer); } } public ArrayList<Customer> queryCommont(String sql , Object ...arg) throws Exception { JDBCUtil util = new JDBCUtil(); java.sql.PreparedStatement ps = null; Connection connection = null; //用来存储查询到的Customers ArrayList<Customer> customers = new ArrayList<>(); try { connection = util.Connection(); ps = connection.prepareStatement(sql); //设置占位符？的值 for (int i = 0; i < arg.length; i++) { ps.setObject(i + 1 , arg[i]); } //获取结果集 ResultSet result = ps.executeQuery(); //获取数据元，这里是反射，可以获取结果集的元 ResultSetMetaData meta = result.getMetaData(); //结果集列数 int columnCount = meta.getColumnCount(); while (result.next()){ Customer customer = new Customer(); for (int i = 0; i < columnCount; i++) { //获取结果集每一列的值 Object value = result.getObject(i + 1); //获取列名 //String name = meta.getColumnName(i + 1); //取代getColumnName,获取列的别名,没有就是本身的列名 String name = meta.getColumnLabel(i + 1).toLowerCase(); //利用反射获得Customer类的对应成员 Field data = Customer.class.getDeclaredField(name); //成员可能是私有的，所以取消权限检测 data.setAccessible(true); //设置成员变量值 data.set(customer , value); } customers.add(customer); } } catch (Exception e) { e.printStackTrace(); }finally { util.Close(connection , ps); return customers; } } //改成泛型 public <T> ArrayList<T> getInstance(Class<T> clazz , String sql , Object ...arg) throws Exception { JDBCUtil util = new JDBCUtil(); java.sql.PreparedStatement ps = null; Connection connection = null; //用来存储查询到的Customers ArrayList<T> ts = new ArrayList<>(); try { connection = util.Connection(); ps = connection.prepareStatement(sql); //设置占位符？的值 for (int i = 0; i < arg.length; i++) { ps.setObject(i + 1 , arg[i]); } //获取结果集 ResultSet result = ps.executeQuery(); //获取数据元，这里是反射，可以获取结果集的元 ResultSetMetaData meta = result.getMetaData(); //结果集列数 int columnCount = meta.getColumnCount(); while (result.next()){ //创建对象 T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { //获取结果集每一列的值 Object value = result.getObject(i + 1); //获取列名 //String name = meta.getColumnName(i + 1); //取代getColumnName,获取列的别名,没有就是本身的列名 String name = meta.getColumnLabel(i + 1).toLowerCase(); //利用反射获得Customer类的对应成员 Field data = clazz.getDeclaredField(name); //成员可能是私有的，所以取消权限检测 data.setAccessible(true); //设置成员变量值 data.set(t , value); } ts.add(t); } } catch (Exception e) { e.printStackTrace(); }finally { util.Close(connection , ps); return ts; } }

批量添加

@Test public void lotInsert() throws Exception { JDBCUtil util = new JDBCUtil(); Connection connection = null; java.sql.PreparedStatement ps = null; try { connection = util.Connection(); //取消每次操作都提交,必须有否则和单个添加一样 connection.setAutoCommit(false); String sql = "insert into goods(name) value(?);"; ps = connection.prepareStatement(sql); long begin = System.currentTimeMillis(); //批量添加 for (int i = 1; i <= 2000; i++) { ps.setObject(1 , "name_" + i); ps.addBatch(); if(i % 500 == 0){ ps.executeBatch(); connection.commit(); ps.clearBatch(); } } //最后再提交 //connection.commit(); long end = System.currentTimeMillis(); System.out.println(end - begin); } catch (Exception e) { e.printStackTrace(); }finally { util.Close(connection , ps); } }

事务管理

@Test public void testTransaction() { JDBCUtil util = new JDBCUtil(); Connection connection = null; try { connection = util.Connection(); //关闭自动提交，当有异常可以回滚到这 connection.setAutoCommit(false); String sql = "update user_table set balance = balance - 100 where user = ?;"; update(connection , sql , "AA"); //int a = 1 / 0; String sql2 = "update user_table set balance = balance + 100 where user = ?;"; update(connection , sql2 , "BB"); System.out.println("转账成功！"); connection.commit(); } catch (Exception e) { try { connection.rollback(); } catch (SQLException throwables) { throwables.printStackTrace(); } e.printStackTrace(); } } public int update(Connection connection , String sql , Object ...arg) throws Exception { JDBCUtil util = new JDBCUtil(); java.sql.PreparedStatement ps = null; try { ps = connection.prepareStatement(sql); for (int i = 0; i < arg.length; i++) { ps.setObject(i + 1 , arg[i]); } return ps.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { //关闭连接默认会提交 util.Close(null , ps); } return 0; }

Druid数据连接池

@Test public void DruidTest() throws Exception { //加载配置文件 Properties properties = new Properties(); InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties"); properties.load(is); //创建数据库连接池 DataSource dataSource = DruidDataSourceFactory.createDataSource(properties); Connection connection = dataSource.getConnection(); System.out.println(connection); }

Druid配置文件

url=jdbc:mysql://localhost:3306/test username=root password=clearlove7 driverClassName=com.mysql.jdbc.Driver #初始数量 initialSize=10 #最大活跃数 maxActive=10

dbutils工具库 增加操作

@Test public void testInsert(){ //dbutils是Apache提供的工具类库，封装了数据库的增删改查操作 QueryRunner queryRunner = new QueryRunner(); Connection connection = null; try { connection = JDBCUtil.Connection(); String sql = "insert into customers(name , email , birth) values(? , ? , ?);"; int update = queryRunner.update(connection , sql , "肖战" , "xiaozhan@qq.com" ,"2000-02-02"); System.out.println(update); } catch (Exception e) { e.printStackTrace(); }finally { try { JDBCUtil.Close(connection , null ,null); } catch (Exception e) { e.printStackTrace(); } } }

查询一条记录操作

@Test public void testQuery(){ QueryRunner runner = new QueryRunner(); try { Connection connection = JDBCUtil.Connection(); String sql = "select * from customers where id = ?;"; //ResultSetHandler接口实现类用于封装一条记录 BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class); Customer customer = runner.query(connection, sql, handler, 25); System.out.println(customer); } catch (Exception e) { e.printStackTrace(); } }

查询多条记录

@Test public void testAllQuery() throws Exception { QueryRunner runner = new QueryRunner(); Connection connection = JDBCUtil.Connection(); //封装多条记录 BeanListHandler<Customer> beanList = new BeanListHandler<>(Customer.class); String sql = "select * from customers"; List<Customer> list = runner.query(connection, sql, beanList); list.forEach(System.out::println); }