JDBC
1.jdbc原理示意图
2.JDBC快速入门
url中
package com.lwb.jdbc.myjdbc;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class jdbc01 {
public static void main(String[] args) throws SQLException {
//1.注册驱动
Driver driver = new Driver();
//2.得到连接
String url = "jdbc:mysql://localhost:3306/lwb_db02";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","lwb");
Connection connect = driver.connect(url, properties);
//3.执行sql
String sql = "INSERT INTO actor VALUES (1,'刘德华','男','1964-06-23','1100')";
Statement statement = connect.createStatement();
int i = statement.executeUpdate(sql);//dml返回影响行数
System.out.println(i>0?"成功":"失败");
//4.关闭连接
statement.close();
connect.close();
}
}
3.数据库连接的五种方式
package com.lwb.jdbc.myjdbc;
import com.mysql.jdbc.Driver;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class jdbcConn {
public static void main(String[] args) throws SQLException, IllegalAccessException, InstantiationException, ClassNotFoundException, IOException {
connect01();
connect02();
connect03();
connect04();
connect05();
}
//方式一
public static void connect01() throws SQLException {
Driver driver = new Driver();
String url = "jdbc:mysql://localhost:3306/lwb_db02";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","lwb");
Connection connect = driver.connect(url, properties);
System.out.println(connect);
connect.close();
}
//方式二
public static void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
//利用反射加载Driver类
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)aClass.newInstance();
String url = "jdbc:mysql://localhost:3306/lwb_db02";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","lwb");
Connection connect = driver.connect(url, properties);
System.out.println(connect);
connect.close();
}
//方式三:利用DriverManager
public static void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
//使用反射创建driver对象
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)aClass.newInstance();
//创建url,user和password
String url = "jdbc:mysql://localhost:3306/lwb_db02";
String user = "root";
String password = "lwb";
//注册Driver驱动
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
}
//方式4:使用Class.forname自动注册驱动
public static void connect04() throws ClassNotFoundException, SQLException {
//使用反射加载了Driver类
Class.forName("com.mysql.jdbc.Driver");
//创建url,user和password
String url = "jdbc:mysql://localhost:3306/lwb_db02";
String user = "root";
String password = "lwb";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
}
//方式5:在方式4的基础上增加配置文件,让mysql更加灵活
public static void connect05() throws IOException, ClassNotFoundException, SQLException {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
}
}
4.ResultSet
查询下面表
package com.lwb.jdbc.myjdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class resultSet {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
//加载驱动
Class.forName(driver);
//连接数据库
Connection connection = DriverManager.getConnection(url, user, password);
//执行sql
Statement statement = connection.createStatement();
//sql语句集
String sql = "select * from actor";
ResultSet resultSet = statement.executeQuery(sql);
//遍历结果集
while(resultSet.next()){
int id = resultSet.getInt(1);//得到第一列
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
Date date = resultSet.getDate(4);
System.out.println(id+","+name+","+sex+","+date);
}
//关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
5.sql语句
5.1 sql注入
statement 会存在sql注入问题,开发过程中一般不会使用
5.2 preparestatement
```java
package com.lwb.jdbc.myjdbc;
import java.io.*;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
public class PrepareStatement {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Scanner sc = new Scanner(System.in);
System.out.println("请输入管理员的名字:");
String admin_name = sc.nextLine();
System.out.println("请输入管理员的密码:" );
String admin_pwd = sc.nextLine();
//获取配置文件信息
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
//获取相关值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
//注册驱动
Class.forName(driver);
//得到连接
Connection connection = DriverManager.getConnection(url, user, password);
//sql语句
String sql ="select * from admin where name = ? and pwd = ?";
//得到preparestatement
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//给问号赋值
preparedStatement.setString(1,admin_name);
preparedStatement.setString(2,admin_pwd);
//执行查询语句,注意括号不能加sql
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
System.out.println("登录成功!");
}else{
System.out.println("登录失败!");
}
//释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}
5.3 预处理DML
package com.lwb.jdbc.myjdbc;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
public class PrepareStatementDML {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Scanner sc = new Scanner(System.in);
System.out.println("请输入管理员的名字:");
String admin_name = sc.nextLine();
System.out.println("请输入管理员的密码:" );
String admin_pwd = sc.nextLine();
//获取配置文件信息
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
//获取相关值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
//注册驱动
Class.forName(driver);
//得到连接
Connection connection = DriverManager.getConnection(url, user, password);
//sql语句
// 1.添加语句
//String sql ="insert into admin values(?,?)";
// 2.修改记录
//String sql = "update admin set pwd = ? where name = ?";
// 3.删除记录
String sql = "delete from admin where name = ?";
//得到preparestatement
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//给问号赋值
preparedStatement.setString(1,admin_name);
//preparedStatement.setString(2,admin_name);
//执行语句,注意括号不能加sql
int row = preparedStatement.executeUpdate();
if(row>0){
System.out.println("执行成功!");
}
//释放资源
preparedStatement.close();
connection.close();
}
}
6.JDBC API小结
7.JDBCUtils封装
7.1 封装代码
package com.lwb.jdbc.util;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
//静态代码块中初始化值
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
user = properties.getProperty("user");
url = properties.getProperty("url");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
} catch (IOException e) {
//将编译异常转换为运行时异常
//调用者可以选择捕获该异常也可以默认处理该异常,比较方便
throw new RuntimeException(e);
}
}
//连接数据库
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException throwables) {
//将编译异常转换为运行时异常
//调用者可以选择捕获该异常也可以默认处理该异常,比较方便
throw new RuntimeException(throwables);
}
}
//关闭资源
public static void close(ResultSet set, Statement statement,Connection connection){
try {
if(set != null){
set.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException throwables) {
throw new RuntimeException(throwables);
}
}
}
7.2 封装测试
package com.lwb.jdbc.util;
import java.sql.*;
public class JDBCUtils_Use {
public static void main(String[] args) {
//testDML();
testSelect();
}
public static void testDML(){
Connection connection = null;
PreparedStatement preparedStatement =null;
try {
//获取连接
connection = JDBCUtils.getConnection();
//编写sql语句
String sql = "update actor set name = ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"林伟波");
preparedStatement.setInt(2,1);
preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
public static void testSelect() {
Connection connection = null;
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
try {
//获取连接
connection = JDBCUtils.getConnection();
//编写sql语句
String sql = "select * from actor";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println(id+","+name);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.close(resultSet,preparedStatement,connection);
}
}
}
8.事务
package com.lwb.jdbc.myjdbc;
import com.lwb.jdbc.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Transaction_ {
public static void main(String[] args) {
// notransaction();
transaction();
}
public static void notransaction(){
//没有事务模拟转账
//1.得到连接
Connection connection = null;
//2.组织sql
String sql1 = "update account set balance = balance - 100 where id = 1";
String sql2 = "update account set balance = balance + 100 where id = 2";
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
//此时id为1的账户减少100元
//由于下面该条语句捕捉到异常,id为2的账户增加钱的语句不执行
int num = 1/0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
//使用事务
public static void transaction(){
//没有事务模拟转账
//1.得到连接
Connection connection = null;
//2.组织sql
String sql1 = "update account set balance = balance - 100 where id = 1";
String sql2 = "update account set balance = balance + 100 where id = 2";
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
//将connection设置为不自动提交
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
//没有自动提交,执行到异常时表中数据并无发生变化
int num = 1/0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
} catch (SQLException throwables) {
//捕捉到异常时进行回滚,即撤销执行的sql
//当不设置保存点时默认回滚到事务刚开始时
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
}
9.批处理
用有无批处理两种方法插入5000条语句
package com.lwb.jdbc.batch;
import com.lwb.jdbc.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Batch_ {
public static void main(String[] args) throws SQLException {
//nobatch();
batch_();
}
//无批处理,无批处理的时间:63199ms
public static void nobatch() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into test values(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i = 0;i<5000;i++){
preparedStatement.setString(1,"111");
preparedStatement.setString(2,"tom"+i);
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("无批处理的时间:"+(end-start));
JDBCUtils.close(null,preparedStatement,connection);
}
//批处理,批处理的时间:532ms
public static void batch_() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into test values(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i = 0;i<5000;i++){
preparedStatement.setString(1,"111");
preparedStatement.setString(2,"tom"+i);
//将sql语句加入到批处理包中
preparedStatement.addBatch();
if((i+1)%1000 == 0){//1000条sql执行一次
//执行批处理包中语句
preparedStatement.executeBatch();
//清空批处理包中语句
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("批处理的时间:"+(end-start));
JDBCUtils.close(null,preparedStatement,connection);
}
}
10.数据库连接池
10.1 传统连接的弊端
10.2 数据库连接池介绍
java程序创建一个数据库连接池,连接池里有若干连接,连接与数据库已经连通,当java程序需要使用时就从数据库连接池中取出一个连接。当连接全部被占用时,java程序想要取出连接就会进入等待队列,等待其他连接被放回数据库连接池时才会取出连接。取出和放回不会使连接与数据库的联系断开。
分类
10.3 C3P0两种连接方式
package com.lwb.jdbc.dataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class C3P0 {
public static void main(String[] args) throws IOException, PropertyVetoException, SQLException {
// testC3P0();
testC3P02();
}
//方式一:相关参数,在程序中指定user,url,password
public static void testC3P0() throws SQLException, PropertyVetoException, IOException {
//1.创建数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//2.通过配置文件获取相关连接信息
Properties properties = new Properties();
properties.load(new FileInputStream("src//mysql.properties"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
//给数据源combopooledDataSource设置相关参数
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
//设置初始化连接数(初始时连接池中的连接数)
comboPooledDataSource.setInitialPoolSize(30);
//设置最大连接数(连接池中连接最多可以添加到的个数)
comboPooledDataSource.setMaxPoolSize(50);
long start = System.currentTimeMillis();
for(int i = 0;i<5000;i++) {
Connection connection = comboPooledDataSource.getConnection();
//System.out.println("连接成功!");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("连接时间:"+(end-start));
}
//第二种方式:使用配置文件模板来完成
public static void testC3P02() throws SQLException {
//1.创建数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");
System.out.println("开始执行。。");
long start = System.currentTimeMillis();
for(int i = 0;i<5000;i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("耗时:"+(end-start));
}
}
10.4 德鲁伊连接池使用
package com.lwb.jdbc.dataSource;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;
public class Druid_ {
//读取配置文件
public static void main(String[] args) throws Exception {
druid01();
}
//Druid连接数据库
public static void druid01() throws Exception {
//读取配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src//druid.properties"));
//创建指定参数的数据库连接池,Druid连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println("连接成功");
connection.close();
}
}
10.5 德鲁伊连接池工具类
工具类代码
package com.lwb.jdbc.dataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtilsByDruid {
private static DataSource ds = null;
//静态代码块初始化ds
static{
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//得到连接
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//关闭连接
public static void close(ResultSet resultSet,Statement statement,Connection connection){
try {
if(resultSet!=null){
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
工具类使用
package com.lwb.jdbc.dataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtilsDruid_use {
public static void main(String[] args) {
testDruidUtils();
}
public static void testDruidUtils() {
Connection connection = null;
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
try {
//获取连接
connection = JDBCUtilsByDruid.getConnection();
//编写sql语句
String sql = "select * from actor";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println(id+","+name);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
}
}
}
10.6 DBUtils
10.6.1 引出
10.6.2 自己编写的代码解决ResultSet问题
(只截取方法部分)
//自己编写代码解决resultSet问题
public static void resultSetToArrayList() {
Connection connection = null;
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
ArrayList<Actor> list = new ArrayList<Actor>();
try {
//获取连接
connection = JDBCUtilsByDruid.getConnection();
//编写sql语句
String sql = "select * from actor";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date birthday = resultSet.getDate("birthday");
String phone = resultSet.getString("phone");
//把得到的记录封装到Actor对象,放入到集合
list.add(new Actor(id,name,sex,birthday,phone));
}
System.out.println(list);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
}
}
10.6.3 DBUtils基本介绍和使用
基本介绍
实际上就是用来执行sql语句的
增删查改
package com.lwb.jdbc.dataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class DBUtils_Use {
public static void main(String[] args) throws SQLException {
// testQueryMary();
// testQuerySingle();
// testScalar();
testDML();
}
//使用apache-DBUtils 工具类+druid完成对表的crud操作
//多行查询
public static void testQueryMary() throws SQLException {
//1.得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2.引入DBUtils相关的jar包,加入Project
//3.创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
//4.执行相关方法返回结果集
String sql = "select * from actor where id >= ?";
//(1)query方法就是执行sql语句,得到resultSet封装到ArrayList集合中
//(2)返回的是一个集合
//(3)sql:被执行的sql语句
//(4)new BeanListHandler(Actor.class)将resultSet集合中的结果封装为对象封装到集合中
// 底层使用反射机制获取Actor的类属性进行封装
//(5)1赋值给sql语句中?,可以有多个值,因为是可变参数
List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
System.out.println("集合信息:");
for(Actor actor : list){
System.out.println(actor);
}
//释放资源
JDBCUtilsByDruid.close(null,null,connection);
}
//单行查询
public static void testQuerySingle() throws SQLException {
//1.得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2.创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
//3.执行相关方法,返回单个对象
String sql = "select * from actor where id = ?";
Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
System.out.println(actor);
//释放资源
JDBCUtilsByDruid.close(null,null,connection);
}
//单行单列查询
public static void testScalar() throws SQLException {
//1.得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2.创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
//3.执行相关方法,返回单个对象
String sql = "select name from actor where id = ?";
Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 1);
System.out.println(obj);
//释放资源
JDBCUtilsByDruid.close(null,null,connection);
}
//演示apache-dbutils+druid完成dml(update,insert,delete)
public static void testDML() throws SQLException {
//1.得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2.创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
//3.组织sql,完成DML操作
// String sql = "update actor set name = ? where id = ?";
// String sql = "insert into actor values(null,?,?,?,?)";
String sql = "delete from actor where id = ?";
//返回受影响行数
//改
// int affectedRow = queryRunner.update(connection, sql, "张三丰", 1);
//增
// int affectedRow = queryRunner.update(connection, sql, "林青霞", "女","2001-03-05","112");
//删
int affectedRow = queryRunner.update(connection, sql, 3);
System.out.println(affectedRow>0?"执行成功":"执行没有影响数据库");
//释放资源
JDBCUtilsByDruid.close(null,null,connection);
}
}
10.7 BasicDAO
10.7.1 引出
10.7.2 应用实例
BasicDAO代码
package com.lwb.jdbc.dao_.dao;
import com.lwb.jdbc.dao_.utils.JDBCUtilsByDruid;
import com.lwb.jdbc.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
//其他DAO的父类
public class BasicDAO<T> {//泛型指定具体类型
private QueryRunner queryRunner = new QueryRunner();
//开发通用的dml方法真对任意对表
public int update(String sql,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int row = queryRunner.update(connection, sql, parameters);
return row;
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JDBCUtils.close(null,null,connection);
}
}
//开发通用的多行查询
public List<T> queryMuti(String sql,Class<T> clazz,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
List<T> list = queryRunner.query(connection, sql, new BeanListHandler<T>(clazz),parameters);
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JDBCUtils.close(null,null,connection);
}
}
//开发通用的单行查询
public T querySingle(String sql,Class<T> clazz,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new BeanHandler<T>(clazz),parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JDBCUtils.close(null,null,connection);
}
}
//开发通用的单行单列查询
public Object queryScalar(String sql,Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new ScalarHandler(),parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JDBCUtils.close(null,null,connection);
}
}
}
ActorDAO代码
package com.lwb.jdbc.dao_.dao;
import com.lwb.jdbc.dao_.domain.Actor;
public class ActorDAO extends BasicDAO<Actor>{
//1.拥有BasicDAO所有的方法
//2.根据业务需求,可以编写特有的方法
}
Actor代码
package com.lwb.jdbc.dao_.domain;
import java.util.Date;
public class Actor {
private Integer id;
private String name;
private String sex;
private Date birthday;
private String phone;
public Actor() {
}
public Actor(Integer id, String name, String sex, Date birthday, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.birthday = birthday;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", phone='" + phone + '\'' +
'}';
}
}
JDBCUtilsByDruid代码
package com.lwb.jdbc.dao_.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtilsByDruid {
private static DataSource ds = null;
//静态代码块初始化ds
static{
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//得到连接
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//关闭连接
public static void close(ResultSet resultSet,Statement statement,Connection connection){
try {
if(resultSet!=null){
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
testDAO代码
package com.lwb.jdbc.dao_.test;
import com.lwb.jdbc.dao_.dao.ActorDAO;
import com.lwb.jdbc.dao_.domain.Actor;
import java.util.List;
public class TestDAO {
public static void main(String[] args) {
//演示多行查询
ActorDAO actorDAO = new ActorDAO();
List<Actor> actors = actorDAO.queryMuti("select * from actor where id >= ?", Actor.class, 1);
System.out.println("===多行查询===");
for (Actor actor : actors) {
System.out.println(actor);
}
//演示单行查询
Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 1);
System.out.println("===单行查询===");
System.out.println(actor);
//演示单行单列查询
Object obj = actorDAO.queryScalar("select name from actor where id = ?",1);
System.out.println("===单行单列查询===");
System.out.println(obj);
//演示dml操作
int update = actorDAO.update("insert into actor values(null,?,?,?,?)", "张无忌", "男", "1999-01-01", "115");
System.out.println(update>=0?"执行成功":"执行失败");
}
}