JavaSE| JDBC
JDBC
JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统（DBMS）、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，使用这个类库可以以一种标
准的方法、方便地访问数据库资源
* JDBC：Java Database Connectivity Java连接数据库技术，一组API。
Java可以连接：MySQL、Oracle、SQL Server 、Redis、MangoDB....
* Java为了可以用统一的方式来连接不同的数据库，使得我们JDBC的代码具有可移植性。
* 如果底层的数据库发生了切换：mysql->oralce，oracle->mysql等
* 尽量的减少Java代码层面的修改。
* SUN设计JDBC的API时，设计了一组接口和一些类。
* 这些接口由数据库厂商来实现，mysql实现了一套，oracle实现了一套....。因为内部如何来通过sql语句操作这个数据库，只有数据厂商自己是最清楚。
这里接口，就是一组操作标准，凡是实现了这个接口的数据库，就可以由Java程序来连接和操作它，否则就不能来连接和使用它。
*
* 换句话说，一个数据库产品想要Java程序来使用它，那么必须实现JDBC这些接口。
* 那么这些由数据库厂商来提供的JDBC接口的实现类，构成了“数据库驱动”。
* JDBC是由SUN公司（现在是Oracle）提供。数据库驱动jar是由各个数据库厂商来提供的。
JDBC的程序开发步骤：
1、要在项目中，引入所使用的数据库的驱动
* （1）把mysql-connector-java-5.1.36-bin.jar放到项目的libs文件夹中
* （2）把这些类引入到编译路径下classpath
* 在jar上，右键->Build Path->Add to Build Path
2、把这个数据库的驱动（JDBC接口的实现类）加载到内存中：注册驱动
Class.forName("com.mysql.jdbc.Driver");
3、获取与数据库的连接
* String url = "jdbc:mysql://localhost:3306/test";
* Connection conn = DriverManager.getConnection(url, "root", "123456");
* mysql是一个服务器端软件，
* 客户端：
* （1）命令行客户端
* mysql -h 主机IP -P 端口号 -u 用户名 -p
* Enter Password:xxx
* （2）SQLyog等可视化工具
* 依然需要指定ip,端口号,用户名,密码,所连接的数据库
* （3）JDBC程序也是一个客户端
* url: jdbc:mysql://localhost:3306/test
* jdbc：主协议； mysql：子协议； localhost：主机IP地址； 3306：端口号； test：数据库
* 如果是oracle数据库的url: jdbc:oracle:thin:@localhost:1521:testdb
* 如果是sql server数据库的url: jdbc:sqlserver://localhost:1433:DatabaseName=testdb
* user：用户名
* password：密码
* 网络编程时学url：协议://主机名:端口号/文件路径
* http://localhost:8080/java1111web/index.html
*
* 错误的演示：
* （1）Access denied for user 'root'@'localhost' (using password: YES)
* 'root'@'localhost'错误或密码错误
* （2）com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
* 检查服务是否开启，网络通信是否正常
* （3）java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
* 检查驱动是否添加到项目的编译路径下 或者是JDK，tomcat等公共的lib下
*
*
*
* 4、操作数据库，与数据库服务器进行通信
* 给服务器传sql，并介绍结果； 通过Statement,PreparedStatement,ResultSet来操作
*
* 5、断开连接
* close()
* 说明：
* （1）当用mysql-connector-java-5.0.8-bin.jar版本的jar时，没有注册驱动的Class.forName(...)的代码报如下错误
* java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test
*
* 当用mysql-connector-java-5.1.36-bin.jar版本的jar时，发现没有加Class.forName(...)的代码也可以运行，因为
* mysql-connector-java-5.1.36-bin.jar版本的jar文件夹下有一个services/java.sql.Driver文件
*/
加载驱动，把驱动类（JDBC接口的实现类）加载到内存；
注册驱动，把驱动类的对象交给DriverManager管理，用于后面创建连接等使用。
Class.forName()
因为 Driver 接口的驱动程序类都包含了静态代码块，在这个静态代码块中，会调用 DriverManager.registerDriver() 方法来注册自身的一个实例，所以可以换一种方式来加载驱动。（即只要想办法让驱动类的这段静态代码块执行即可注册驱动类，而要让这段静态代码块执行，只要让该类被类加载器加载即可）
调用 Class 类的静态方法 forName()，向其传递要加载的 JDBC 驱动的类名。
//通过反射，加载与注册驱动类，解耦合（不直接依赖）Class.forName("com.mysql.jdbc.Driver");获取数据库链接
可以通过 DriverManager 类建立到数据库的连接Connection：
DriverManager 试图从已注册的 JDBC 驱动程序集中选择一个适当的驱动程序。
public static Connection getConnection(String url)
public static Connection getConnection(String url,String user, String password)
public static Connection getConnection(String url,Properties info)其中Properties info通常至少应该包括 "user" 和 "password" 属性
JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。JDBC URL的标准由三部分组成，各部分间用冒号分隔。
jdbc:<子协议>:<子名称>
协议：JDBC URL中的协议总是jdbc
子协议：子协议用于标识一个数据库驱动程序
子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息
例如：
操作或访问数据库
数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。
其实一个数据库连接就是一个Socket连接。
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
PrepatedStatement：SQL 语句被预编译并存储在此对象中，然后可以使用此对象多次高效地执行该语句。
CallableStatement：用于执行 SQL 存储过程
2. Statement
通过调用 Connection 对象的 createStatement() 方法创建该对象，该对象用于执行静态的 SQL 语句，并且返回执行结果。
Statement 接口中定义了下列方法用于执行 SQL 语句：
int excuteUpdate(String sql)：执行更新操作INSERT、UPDATE、DELETE
ResultSet excuteQuery(String sql)：执行查询操作SELECT
ResultSet
通过调用 Statement 对象的 excuteQuery() 方法创建该对象
ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商实现
ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行
ResultSet 接口的常用方法：
boolean next()
getXxx(String columnLabel)：columnLabel使用 SQL AS 子句指定的列标签。如果未指定 SQL AS 子句，则标签是列名称
getXxx(int index) :索引从1开始
释放资源
Connection、Statement、ResultSet都是应用程序和数据库服务器的连接资源，使用后一定要关闭，可以在finally中关闭
Connection的对象调用createStatement( )方法用于执行sql语句
Statement的增删改查
* 增删改查
*
* 1、DriverManager：管理数据库驱动，通过它可以获取连接
* DriverManager.getConnection(...)
* 2、Connection：代表一个数据库连接对象
* 可以创建Statement对象
* 3、Statement：可以执行sql
* executeUpdate(insert或update或delete或DDL语句)。
* executeQuery(select语句)
* 4、 ResultSet：包含了查询的结果
* 通过遍历结果集来获取查询结果
*
* boolean next()：判断是否有下一条记录
* xx getXxx(第几列)
* xx getXxx(字段名称或别名)
@Test public void test1() throws Exception{ Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); //String sql = "select * from t_employee"; //查找 String sql2 = "DELETE FROM t_department WHERE dname = '测试部'"; //删除操作; String sql3 = "INSERT INTO t_department (did, dname, description) VALUES (6, '测试部', '负责测试工作')";//添加 String sql4 = "UPDATE t_department SET dname = '找bug' WHERE dname = '测试部'"; //修改操作 Statement st = conn.createStatement();
int eu = st.executeUpdate(sql4);//删除,添加,修改 //ResultSet rs = st.executeQuery(sql); //查找 System.out.println(eu); //com.mysql.jdbc.JDBC4ResultSet@7a92922 /*if(rs.next()){ int count = rs.getInt("eid"); //String ename = rs.getString("ename"); //只是获取一个 String ename = rs.getString(2); //等同于rs.getString("ename"); String tel = rs.getString("tel"); System.out.println("姓名:"+ ename + "电话" + tel); }*/ st.close(); conn.close(); }
Statement的问题： 1、拼接麻烦 String sql = "INSERT INTO t_employee(ename,tel,gender,salary,birthday)VALUES('" + name +"','"+tel+"','"+gender+"',"+salary+",'"+birth+"')"; 2、SQL的注入
SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令，从而利用系统的 SQL 引擎完成恶意行为的做法。对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement 取代 Statement 就可以了。
3、处理Blob等二进制类型的数据
BLOB (binary large object)，二进制大对象，BLOB常常是数据库中用来存储二进制文件的字段类型。
插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)
实际使用中根据需要存入的数据大小定义不同的BLOB类型。
需要注意的是：如果存储的文件过大，数据库的性能会下降。
3. PreparedStatement
可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象
PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值
ResultSet executeQuery()执行查询，并返回该查询生成的 ResultSet 对象。
int executeUpdate()：执行更新，包括增、删、该
用法对比：
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://loclhost:3306/test", "root", "123456");
//使用Statement
String sql = "insert into student values (null, 'kk', 22)";
conn.createStatement().executeUpdate(sql); //增、删、改
ResultSet rs = conn.createStatement().executeQuery(sql); //返回结果是一个ResultSet对象
if(rs.next()){
String sname = rs.getString(2); //等同于rs.getString("sname");
}
//使用preparedstatement
String sql2 = "insert into student values (null, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql2);
ps.setString(1, "kris");
ps.setInt(2, 22);
ps.executeUpdate(); //executeUpdate()和executeQuery()用法一样
@Test public void test1() throws Exception{ Scanner input = new Scanner(System.in); System.out.println("姓名:"); String ename = input.next(); System.out.println("电话:"); String tel = input.next(); System.out.println("薪资:"); double salary = input.nextDouble(); System.out.println("性别:"); char gender = input.next().charAt(0); System.out.println("生日:"); String birth = input.next(); //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); //3.编写sql语句 String sql = "insert into t_employee (ename, tel, salary, gender, birthday) values (?,?,?,?,?)"; //"INSERT INTO t_employee(ename,tel,gender,salary,birthday)VALUES(?,?,?,?,?)"; //4.创建PreparedStatement对象 PreparedStatement ps =//5.设置值 ps.setString(1, ename); ps.setString(2, tel); ps.setDouble(3, salary); ps.setString(4, gender + ""); //ps.setString(5, birthday); //要把String变成date类型 /* SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date parse = sdf.parse(birth); java.sql.Date birthday = new java.sql.Date (parse.getTime());//转成毫秒 ps.setDate(5, birthday); */
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(birth);
//转成Date类型,这里的Date是java.util包里的Date,要把它转成java.sql里的Date
java.sql.Date birthday = new java.sql.Date(date.getTime()); //long类型
ps.setDate(5, birthday);
int len = ps.executeUpdate();
System.out.println(len);
ps.close();
conn.close();
}
@Test
public void test2() throws Exception{
Scanner input = new Scanner(System.in);
System.out.print("姓名:");
String name = input.nextLine(); //next是遇到空格就停止,所以这里用nextLine();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
String sql = "SELECT eid, ename, tel, gender, salary, birthday FROM t_employee where ename = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name);
ResultSet rs = ps.executeQuery();
while(rs.next()){
int eid = rs.getInt(1); //不是从0开始的
String ename = rs.getString(2);
String tel = rs.getString(3);
String gender = rs.getString(4);
double salary = rs.getDouble(5);
Date birthday = rs.getDate(6);
System.out.println(eid + "\t" + ename + "\t" + tel + "\t" + gender + "\t" + salary + "\t" + birthday);
}
rs.close();
conn.close();
}
@Test //处理Blob类型的数据
public void test3()throws Exception{
//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2、获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
//3、编写sql
FileInputStream fis = new FileInputStream("16.gif");
String sql = "INSERT INTO user values(?,?,now(),?)";
//4、创建一个Statement对象
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, "kris");
st.setString(2, "1234");
st.setBlob(3, fis);
//5、执行sql
int len = st.executeUpdate();
//6、处理结果
System.out.println(len>0?"添加成功":"添加失败");
//7、断开连接
st.close();
conn.close();
}
获取自增长的键值
获取自增长的键值：
（1）在创建PreparedStatement对象时
原来：PreparedStatement pst = conn.preparedStatement(sql);
现在：PreparedStatement pst = conn.prepareStatement(orderInsert,Statement.RETURN_GENERATED_KEYS);
（2）原来执行更新
原来：int len = pst.executeUpdate();
现在：int len = pst.executeUpdate();
ResultSet rs = pst.getGeneratedKeys();
if(rs.next()){
Object key = rs.getInt(第几列);//获取自增长的键值
}
* 在添加时，如何获取自增长的键值
*
* 1、如何获取PreparedStatement对象
* PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
* 2、获取自增长的键值
ResultSet rs = pst.getGeneratedKeys();
if(rs.next()){
int did = rs.getInt(1);
System.out.println("新部门的编号：" + did);
}
批处理
当需要成批插入或者更新记录时。可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。
JDBC的批量处理语句包括下面两个方法：
addBatch()：添加需要批量处理的SQL语句或参数
executeBatch()：执行批量处理语句；
通常我们会遇到两种批量执行SQL语句的情况：
多条SQL语句的批量处理；
一个SQL语句的批量传参；
注意：
JDBC连接MySQL时，如果要使用批处理功能，请再url中加参数 ?rewriteBatchedStatements=true
PreparedStatement作批处理插入时使用values（使用value没有效果）
批处理：
* （1）PreparedStatement的对象.addBatch()；先添加到批处理的缓存区
* （2）PreparedStatement的对象.executeBatch()；统一处理批处理
* 批处理的功能默认没有打开，我们需要在建立连接时，告知mysql打开这个批处理的功能。
rewriteBatchedStatements=true
url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true"
* 回忆：
* http://localhost:8080/java1111/login?username=chai&password=123
* 提示：添加语句时，不要用value用values
@Test
public void test1() throws Exception{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
Connection conn = DriverManager.getConnection(url, "root", "123456");
String sql = "insert into student values (null, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
try {
for(int i = 1; i < 1000; i++){ //循环设置?的值
ps.setString(1, "kk" + i);
ps.setInt(2, i);
ps.addBatch();// 先添加到批处理的缓存
}
ps.executeBatch(); //统一处理批处理
System.out.println("添加成功");
} catch (Exception e) {
System.out.println("出错了");
}
ps.close();
conn.close();
}
事务
JDBC程序中当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
JDBC程序中为了让多个 SQL 语句作为一个事务执行：（重点）
调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
在其中某个操作失败或出现异常时，调用 rollback(); 方法回滚事务
若此时 Connection 没有被关闭, 则需要恢复其自动提交状态 setAutoCommit(true);
注意：
如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下
* JDBC如何实现事务管理。
* 1、开启事务
* Connection连接对象.setAutoCommit(false);//开启手动提交模式
*
* 2、执行事务中的Sql
* 和原来一样
*
* 3、提交或回滚
* Connection连接对象.commit();
* Connection连接对象.rollback();
*
* 如果都正确，就提交
* 如果有问题，就回滚
*
* 补充：因为后面的时候，我们的连接对象可能是从“数据库连接池”中获取，用完之后要放回去，
* 即这个连接对象是“重复”使用的，就算close()，它也不会断开，而是“放回”连接池。
*
* 4、
* 在关闭连接之前，恢复连接对象的自动提交模式，Connection连接对象.setAutoCommit(true);
* Connection连接对象.close()
JDBC程序中当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
JDBC程序中为了让多个 SQL 语句作为一个事务执行：（重点）
调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
在其中某个操作失败或出现异常时，调用 rollback(); 方法回滚事务
若此时 Connection 没有被关闭, 则需要恢复其自动提交状态 setAutoCommit(true);
注意：
如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下
@Test public void test1() throws ClassNotFoundException, SQLException{ Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); conn.setAutoCommit(false); String sql1 = "insert into student values (null, ?, ?)"; //添加 String sql2 = "update student set sname = ? when sname = ?"; //修改 //创建一个statement对象 PreparedStatement ps1 = null; PreparedStatement ps2 = null; try { ps1 = conn.prepareStatement(sql1); ps1.setString(1, "kk"); ps1.setInt(2, 18); ps1.executeUpdate(); //修改,可以同时添加同时把它修改了 ps2 = conn.prepareStatement(sql2); ps2.setString(1, "kris"); ps2.setString(2, "kk"); ps2.executeUpdate(); conn.commit(); System.out.println("添加成功"); System.out.println("修改成功"); } catch (SQLException e) { conn.rollback();//如果有异常,就回滚; 有一个sql出错,另外一个就不会插入到数据库中,回滚 System.out.println("添加失败"); System.out.println("修改失败"); } //断开连接 ps1.close(); ps2.close(); conn.setAutoCommit(true); //需要恢复其自动提交状态 setAutoCommit(true); conn.close(); }
4. 数据库连接池
JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现
* 数据库连接池：也称为数据源 DataSource
*
* 1、为什么需要数据库连接池？
* （1）如果没有连接池，DB服务器可能会超过最大连接数的极限，会挂掉
* （2）每次建立连接（TCP/IP），它需要“三次握手”来建立连接，比较费事，如果这一次连接SQL比较简单，一下就完成了，那么这次连接的成本就太高了
* （3）甚至有的程序员，可能在编写代码时，用完连接后，忘了关闭了，数据库很快就down了
*
* 为了数据库服务器的性能和安全，我们可以在Java程序与数据库服务器之间建立一个数据库连接池。
*
* 数据库连接池，在一开始先“准备”一下连接（初始化连接数），下次Java程序需要连接时，从这个池中直接获取，因为已经提前连接好了，那么直接拿连接对象，速度非常快，
* 响应速度更快。
* 数据库连接池会设置一个“最大连接数”，如果一旦池中的连接数达到“最大连接数”，它会让Java程序等待或等待一段时间异常，这样可以保证数据库服务器不会挂掉，因为
* Java程序中可以处理 异常。
* 数据库连接池中的连接是重复使用的，这里要注意一些连接的属性设置，再换回去之前要还原，setAutoCommit(true)等。
*
* 有了数据库连接池，JDBC的程序，修改的“获取连接”的方式而已，其他的没有改。
*
* 原来：
* （1）Class.forName(xxx)
* （2）Connection con = DriverManager.getConnection(...)
*
* 现在：
*
*
* 使用数据库连接池的步骤：
* 1、加入支持的jar
* (1)把druid-1.1.10.jar加入到libs文件夹中
* (2)添加到Build Path中
*
* 2、设置数据库连接池的参数
* （1）在代码中setProperty(key,value)
* （2）可以配置文件中配置
*
* 3、建立数据库连接池
* DataSource ds = DruidDataSourceFactory.createDataSource(pro);
*
* 4、获取连接
*/
Druid（德鲁伊）数据源
druid.properties
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=2000
filters=wall
public class TestPool { public static void main(String[] args) throws Exception{ //为什么必须要在main方法中呢,单独Test不运行 Properties pro = new Properties(); /*单独放到一个文件中 pro.setProperty("url", "jdbc:mysql://localhost:3306/test"); pro.setProperty("username", "root"); pro.setProperty("password", "123456"); pro.setProperty("driverClassName", "com.mysql.jdbc.Driver"); pro.setProperty("initialSize", "5"); pro.setProperty("maxActive", "10");*/(TestPool.class.getClassLoader().getResourceAsStream("druid.properties")); DataSource ds =// System.out.println(conn); //ds.getConnection(); -> com.mysql.jdbc.JDBC4Connection@79b4d0f System.out.println(ds); /*for(int i = 1; i <= 15; i++){ Connection conn = ds.getConnection(); System.out.println(i + "连接" + conn); conn.close(); }*/ for(int i = 1; i <= 15; i++ ){ new Thread("第" + i + "个"){ public void run(){ try { Connection conn = ds.getConnection(); System.out.println(getName() + "连接" + conn); Thread.sleep(1000); conn.close(); //这里没有conn.close()，相当于没有还回连接池,访问结束后放到连接池 } catch (Exception e) { e.printStackTrace(); } } }.start(); } System.out.println(ds); } }
根据DruidDataSour自定义一个JDBCTools工具类
package com.atguigu.pool; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; /** JDBC工具类提供的方法： * （1）获取连接 * （2）释放资源 * （3）初始化信息 * * 注意：整个项目连接池只要一个就够了*/ public class JDBCTools { private static DataSource ds; //import javax.sql.DataSource static{ Properties pro = new Properties(); try { pro.load(JDBCTools.class.getClassLoader().getResourceAsStream("druid.properties")); ds = DruidDataSourceFactory.createDataSource(pro); } catch (Exception e) { e.printStackTrace(); } } //获取连接的信息 public static DataSource getDs(){ return ds; } //获取连接的方法 如:com.mysql.jdbc.JDBC4Connection@79b4d0f public static Connection getConnection() throws SQLException{ return ds.getConnection(); } //关闭连接的方法,抛出异常 public static void free(Connection conn) throws SQLException{ if(conn != null){ conn.close(); } } //关闭连接方法,不会抛出异常; 带Quietly的就是没有throws异常; try, catch了 public static void freeQuietly(Connection conn){ if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } //关闭Statement对象 //这个方法既可以接收Statement的对象，也可以接收它的子接口PreparedStatement的对象 public static void freeQuietly(Statement st){ if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } } //关闭ResuleSet对象 public static void freeQuietly(ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } //关闭ResultSet、Statement、Connection对象 public static void freeQuietly(ResultSet rs, Statement st, Connection conn){ freeQuietly(rs); freeQuietly(st); freeQuietly(conn); } }
public static void main(String[] args) throws SQLException {
Connection conn = JDBCTools.getConnection(); //获取连接
String sql = "insert into student values (null, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "kris");
ps.setInt(2, 22);
int len = ps.executeUpdate();
System.out.println(len);
JDBCTools.freeQuietly(null, ps, conn); //关闭操作
}
5. Apache--JDBC工具类
* commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，
* 学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
* 使用步骤
* 1、引入jar
* (1)把commons-dbutilsxx.jar放到libs中
* (2)添加到Build Path中
*
* QueryRunner类：
* 该类封装了SQL的执行，是线程安全的。
（1）可以实现增、删、改、查、批处理、
（2）考虑了事务处理需要共用Connection。
（3）该类最主要的就是简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。
*
* （1）通用的增、删、改
* QueryRunner对象.update(sql,参数)
* （2）通用的添加
* QueryRunner对象.insert(sql,自增长键值的结果处理器,参数)
* （3）通用的查询
* QueryRunner对象.query(sql,结果集处理器对象,参数)
* （4）通用的批处理
* QueryRunner对象.batch(sql,参数)
* QueryRunner对象.insertBatch(sql,自增长键值的结果处理器,参数)
*
*
* ResultSetHandler<T> rsh：结果集处理器，是一个接口，它有很多实现类
* (1)ScalarHandler：单个值的处理器； 例如：查询总记录数，返回自增长键值等
* (2)BeanListHandler：多个JavaBean对象的处理器
* (3)BeanHandler：一个JavaBean对象的处理器
* (4)MapListHandler
* ....
*
* 回忆：
* InvocationHandler：代理工作处理器
*
*
* 特别说明：
* 当使用QueryRunner时，如果多个sql语句要考虑事务，那么就需要单独传Connection对象，并且要
* （1）conn.setAutoCommit(false);//设置为手动提交模式
* （2）qr.update/query等方法(conn, ...);
* （3）conn.commit();或conn.rollback();
* （4）conn.setAutoCommit(true);
* （5）关闭连接
*/
public class TestDBUtils { @Test //添加操作 public void test1() throws SQLException{ QueryRunner qr = new QueryRunner(JDBCTools.getDs()); //获取操作池 int len = qr.update("insert into student values (null, ?, ?)", "静", 22); System.out.println(len); String sql = "insert into student values (null, ?, ?)"; Long insert = qr.insert(sql, new ScalarHandler<Long>(), "kk", "33"); System.out.println(insert); //返回自增长键值; } @Test //查询操作 public void test2() throws SQLException{ QueryRunner qr = new QueryRunner(JDBCTools.getDs()); //"select * from e_employee" 也可以 String sql = "SELECT eid,ename,tel,gender,salary,commission_pct AS commissionPct,birthday,hiredate,job_id AS jobId,email,`mid`,address,native_place AS nativePlace, did FROM t_employee"; System.out.println(sql); List<Employee> query = qr.query(sql, new BeanListHandler<Employee>(Employee.class)); for (Employee employee : query) { System.out.println(employee); } String sql1 = "SELECT eid,ename,tel,gender,salary,commission_pct AS commissionPct,birthday,hiredate,job_id AS jobId,email,`mid`,address,native_place AS nativePlace, did FROM t_employee where eid = ?"; Employee emp = qr.query(sql1, new BeanHandler<Employee>(Employee.class), 2); System.out.println(emp); //假设要计算总页数 = 总记录数 / 每页显示的记录数 ;要考虑是否整除 //每页显示20行 String sql2 = "select count(*) from t_employee"; Long count = qr.query(sql2, new ScalarHandler<Long>()); if(count % 20 == 0){ System.out.println("总页数:" + count/20); }else{ System.out.println("总页数" + (count/20 + 1)); } } @Test //QueryRunner,封装到map、List中 public void test3() throws SQLException{ QueryRunner qr = new QueryRunner(JDBCTools.getDs()); String sql = "select did, avg(salary) from t_employee group by did"; List<Map<String, Object>> list = qr.query(sql, new MapListHandler()); //每一行是一个map，很多行是一个List for (Map<String, Object> map : list) { Set<Entry<String, Object>> entrySet = map.entrySet(); for (Entry<String, Object> entry : entrySet) { System.out.println(entry); } } } @Test //事务 public void test4() throws SQLException{ String sql1 = "insert into student values (null, ?, ?)"; String sql2 = "update student set sname = ? where sname = ?"; QueryRunner qr = new QueryRunner(JDBCTools.getDs()); Connection conn = JDBCTools.getConnection(); conn.setAutoCommit(false); try { qr.update(conn, sql1, "kk", 22); qr.update(conn, sql2, "kris", "kk"); System.out.println("添加修改成功"); conn.commit(); } catch (Exception e) { conn.rollback(); System.out.println("失败"); } conn.setAutoCommit(true); JDBCTools.free(conn); } }
表与JavaBeam
通过给列取别名的方式，来告知数据库的列名与其对应实体的属性名
6. DAO和增删改查通用方法
DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息
作用：为了实现功能的模块化，更有利于代码的维护和升级。
通用的增删改查方法
public class BasicDAO {
//通用的增删改的方法
//Object... args：给sql中的?设置值用的，有个实参，就说明有几个？需要设置
public static int update(String sql, Object...args) throws SQLException{
Connection conn = JDBCTools.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
if(args != null && args.length > 0){
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
}
int len = ps.executeUpdate();
JDBCTools.freeQuietly(null, ps, conn);
return len;
}
//通用的查询多个对象的操作
public static <T> ArrayList<T> query(Class<T> clazz, String sql, Object... args ) throws SQLException, Exception, Exception{
Connection conn = JDBCTools.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
if(args != null && args.length > 0){
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
}
ResultSet rs = ps.executeQuery();
ArrayList<T> list = new ArrayList<>();
//在我们的ResultSet对象中，不仅仅有数据（记录），还有关于“字段名”等信息
//获取结果集的元数据
ResultSetMetaData metaData = rs.getMetaData();
int count = metaData.getColumnCount(); //一共查询了几列;
while(rs.next()){
T t = clazz.newInstance(); //一行代表一个对象
//动态设置属性值t
for(int i = 0; i < count; i++){ //获取第几列的字段名
String fileName = metaData.getColumnLabel(i+1); //获取第几列的字段名
Field f = clazz.getDeclaredField(fileName);
f.setAccessible(true);
f.set(t, rs.getObject(fileName));
}
list.add(t);
}
JDBCTools.freeQuietly(rs, ps, conn);
return list;
}
public class TestBaseDAO {
@Test //添加
public void test1() throws SQLException{
String sql = "INSERT INTO student VALUES (null, ?, ?)";
int len = BasicDAO.update(sql, "kris",22);
}
@Test //修改
public void test2() throws SQLException{
String sql = "update student set sname = ? where id = ?";
BasicDAO.update(sql, "静静", 17);
System.out.println("修改成功");
}
@Test //删除
public void test3(){
String sql = "delete from student where id > ? ";
try {
BasicDAO.update(sql, 15);
System.out.println("删除成功");
} catch (SQLException e) {
System.out.println("删除失败");
e.printStackTrace();
}
}
@Test //查询操作
public void test4() throws SQLException, Exception{
//String sql = "select * from t_department";
//String sql2 = "select * from t_employee";
//Employee类中的属性名：commissionPct，而我们的数据库中的字段名：commission_pct
//ArrayList<Department> list = BasicDAO.query(Department.class, sql);
//如果表中的字段名与类的属性名不一致，可以通过取别名的方式来解决
//如果报java.lang.IllegalArgumentException: Can not set double field com.atguigu.bean.Employee.commissionPct to null value
//原因是数据库中的所有类型的字段都可能是null值，而Java中的基本数据类型是不能用null值，所以可以把基本数据类型修改为它的包装类
String sql2 = "SELECT eid,ename,tel,gender,salary,commission_pct AS commissionPct,birthday,hiredate,job_id AS jobId,email,`mid`,address,native_place AS nativePlace, did FROM t_employee";
ArrayList<Employee> list2 = BasicDAO.query(Employee.class, sql2);
for (Employee depart: list2) {
System.out.println(depart);
}
}
public class BasicDAOImpl { private QueryRunner qr = new QueryRunner(); //通用的增删改查 public int update(String sql, Object... params) throws SQLException{ Connection conn = JDBCTools.getConnection(); int len = 0; try { len = qr.update(conn, sql, params); } catch (SQLException e) { e.printStackTrace(); } return len; } //获取一个对象 public <T> T getBean(Class<T> clazz, String sql, Object... params) throws SQLException{ Connection conn = JDBCTools.getConnection(); T t = null; try { t = qr.query(conn, sql, new BeanHandler<>(clazz), params); } catch (Exception e) { e.printStackTrace(); } return t; } //获取所有的对象 public <T> List<T> getBeanList(Class<T> clazz, String sql, Object params) throws SQLException{ Connection conn = JDBCTools.getConnection(); List<T> list = null; try { list = qr.query(conn, sql, new BeanListHandler<T>(clazz), params); } catch (SQLException e) { e.printStackTrace(); } return list; } //获取一个单一值的方法,专门执行像select * from... public Object getSingleValue(String sql, Object...params) throws SQLException{ Connection conn = JDBCTools.getConnection(); Object value = null; try { value = qr.query(conn, sql, new ScalarHandler(), params); } catch (Exception e) { e.printStackTrace(); } return value; } /** 进行批处理的方法 * 关于二维数组Object[][] params * 二维数组的第一维是sql语句要执行的次数 * 二维数组的第二维就是每条sql语句中要填充的占位符*/ public void batchUpdate(String sql, Object[][] params) throws SQLException{ Connection conn = JDBCTools.getConnection(); try { qr.batch(conn,sql, params); } catch (Exception e) { e.printStackTrace(); } }
