1、序言
数据持久化：把数据保存到可掉电式存储设备中以供之后使用。持久化的过程一般情况下通过各种关系数据库来完成。持久化主要应用是将内存中的数据存储到关系数据库中。
JDBC是接口，一组规范，JDBC以下连接的是子类（MySQL、Oracle，SQLServer、DB2等数据库），java应用程序通过JDBC（接口）连接数据库，面向接口编程。
JDBC底层的实现子类称为驱动。java.sql.Driver接口是所有JDBC驱动程序需要实现的接口。
在java程序中导入MySQL驱动：
（1）在项目下创建目录
（2）把相关的.jar文件复制到这个目录中
（3）再把mysql-connector-java-5.1.7-bin.jar、mysql-connector-java-5.1.7-src.zip两个文件导入到build-path中（在 IDEA 中 Build-path：File -- Project Structure -- Modules -- 选中需要添加 build-path 的项目 -- dependencies -- 点击右边的小加号 -- 选择JARs or directories -- 选择要添加的外部jar包）
数据库驱动程序下载：https://download.csdn.net/download/weixin_43923463/78395674
2、连接数据库
直接通过驱动程序连接本机数据库：
@Test
public void test() throws SQLException {
Driver driver = new com.mysql.jdbc.Driver();
//url：uniform resource locator 统一资源定位符
//主协议:子协议://MySQL服务器主机地址:MySQL服务器端口号/数据库名
String url = "jdbc:mysql://127.0.0.1:3306/jdbc";
Properties info = new Properties();
info.setProperty("user","root"); //用户名
info.setProperty("password","hike");//密码
Connection connect = driver.connect(url,info); //直接通过驱动程序连接
System.out.println(connect);
}
使用驱动程序管理器连接数据库：
@Test
public void test1() throws SQLException {
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://127.0.0.1:3306/jdbc";
String user = "root";
String password = "hike";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
}
通过反射使用驱动程序管理器连接数据库，标准做法：
@Test
public void test2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
//Driver driver = new com.mysql.jdbc.Driver();
Class clazz = Class.forName("com.mysql.jdbc.Driver"); //在类的静态语句块中，会自动完成自我注册
//Driver driver = (Driver) clazz.newInstance();
//DriverManager.registerDriver(driver);
String url = "jdbc:mysql://127.0.0.1:3306/jdbc";
String user = "root";
String password = "hike";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
}
通过配置文件连接数据库
编写配置文件jdbc.properties。注意每一行的末尾不能有空格。
driverClassName = com.mysql.jdbc.Driver url = jdbc:mysql://127.0.0.1:3306/jdbc user = root password = hike
@Test
public void test3() throws IOException, ClassNotFoundException, SQLException {
InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(inputStream);
inputStream.close();
Class.forName(properties.getProperty("driverClassName"));
Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties);
System.out.println(connection);
}
将连接、关闭数据库方法封装成工具类
public class JDBCUtil {
//如果连接失败，需要将问题报告给调用者，不能私自把问题压下来，所以需要抛出异常
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(inputStream);
inputStream.close();
Class.forName(properties.getProperty("driverClassName"));
Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties);
return connection;
}
public static void close(Connection connection) {
if(connection != null){
try{
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
@Test
public void test4(){
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
System.out.println(connection);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection);
}
}
3、JDBC的API Statement PreparedStatement
连接数据库后想要对数据库进行 *** 作需要使用连接对象获取执行体对象。
使用Statement创建表，向表中插入数据
@Test
public void test5(){
Connection connection = null; //连接对象
Statement statement = null; //执行体对象
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement(); //通过连接对象获取执行体对象
System.out.println(statement);
String sql = "create table if not exists user(id int auto_increment," +
"username varchar (20)," +
"password varchar (30)," +
"primary key (id)," +
"unique (username))";
int rows = statement.executeUpdate(sql);//update,delete,insert,DDL,DML
System.out.println(rows + " rows affected");
sql = "insert into user(username,password) values ('admin','admin')";
rows = statement.executeUpdate(sql);
System.out.println(rows + " rows affected");
sql = "insert into user(username,password) values ('root','root')";
rows = statement.executeUpdate(sql);
System.out.println(rows + " rows affected");
sql = "insert into user(username,password) values ('user','user')";
rows = statement.executeUpdate(sql);
System.out.println(rows + " rows affected");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection,statement);
}
}
Statemen缺点：statement有SQL注入的风险，并且效率低。
一般使用PreparedStatement预编译，没有SQL注入风险，效率高。
创建表customer，包含属性id，name，gender，age，phone，并插入几条数据
@Test
public void test6(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "create table if not exists customer(" +
"id int auto_increment," +
"name varchar (20)," +
"gender enum('男','女')," +
"age int," +
"phone varchar (11)," +
"primary key(id)" +
")";
preparedStatement = connection.prepareStatement(sql);
int rows = preparedStatement.executeUpdate();
System.out.println(rows +" rows affect");
sql = "insert into customer(name,gender,age,phone) values (?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
//执行预编译前一定要先解决?
preparedStatement.setString(1,"李四");//把sql中的第一个？替换成参数2
preparedStatement.setString(2,"女");
preparedStatement.setInt(3,26);
preparedStatement.setString(4,"21111111111");
rows = preparedStatement.executeUpdate(); //执行这个sql语句
System.out.println(rows +" rows affect");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement);
}
}
练习：创建teacher表，包含属性id，name，phone，gender，age，address，并插入几条数据
@Test
public void test7() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection(); //连接数据库
//准备sql语句
String sql = "create table if not exists teacher(" +
"id int auto_increment," +
"name varchar (20)," +
"phone char (11)," +
"gender enum('男','女')," +
"age int," +
"address varchar (50)," +
"primary key(id)" +
")";
preparedStatement = connection.prepareStatement(sql); //预编译
int rows = preparedStatement.executeUpdate(); //执行
System.out.println(rows + " rows afect");
JDBCUtil.close(null,preparedStatement); //关闭资源
sql = "insert into teacher(name,phone,gender,age,address) values (?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
//插入第一条数据
preparedStatement.setString(1,"小张");
preparedStatement.setString(2,"12345678912");
preparedStatement.setString(3,"男");
preparedStatement.setInt(4,45);
preparedStatement.setString(5,"北京朝阳");
rows = preparedStatement.executeUpdate();
System.out.println(rows + " rows affect");
//插入第二条数据
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"小张1");
preparedStatement.setString(2,"12345678912");
preparedStatement.setString(3,"男");
preparedStatement.setInt(4,45);
preparedStatement.setString(5,"北京朝阳");
rows = preparedStatement.executeUpdate();
System.out.println(rows + " rows affect");
//插入第三条数据
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"小张2");
preparedStatement.setString(2,"12345678912");
preparedStatement.setString(3,"男");
preparedStatement.setInt(4,45);
preparedStatement.setString(5,"北京朝阳");
rows = preparedStatement.executeUpdate();
System.out.println(rows + " rows affect");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection, preparedStatement);
}
}
改进处理预编译后处理？的方式（使用Object数组处理问号）
@Test
public void test8(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "insert into customer(name,gender,age,phone) values (?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
//使用Object数组处理问号
Object[] args = {"王五","女",23,"55555555555"};
for(int i = 0; i < args.length; i++){
preparedStatement.setObject(i + 1,args[i]);
}
int rows = preparedStatement.executeUpdate();
System.out.println(rows + " rows affect");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement);
}
}
除了sql语句和要插入的数据，程序的其他内容并不会变化，所以可以将通用的部分抽取出去，成为一个通用的方法。
public class CommonUtil {
//公共更新 *** 作
//有了异常不要私自扣下，发送给调用者
public static int update(String sql,Object[] args) throws SQLException, IOException, ClassNotFoundException {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
preparedStatement.setObject(i + 1,args[i]);
}
int rows = preparedStatement.executeUpdate();
return rows;
}finally {
JDBCUtil.close(connection,preparedStatement);
}
}
}
@Test
public void test9(){
String sql = "insert into customer(name,gender,age,phone) values (?,?,?,?)";
Object[] args = {"赵六","男",28,"66666666666"};
try {
int rows = CommonUtil.update(sql, args);
System.out.println(rows + " rows affect");
} catch (Exception e) {
e.printStackTrace();
}
}
将Object数组变为可变参数Object...，使得程序更加灵活。
//有了异常不要私自扣下，发送给调用者
public static int update(String sql,Object... args) throws SQLException, IOException, ClassNotFoundException {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection(); //从数据库获得连接
preparedStatement = connection.prepareStatement(sql); //预编译传入的sql语句
for(int i = 0; i < args.length; i++){
preparedStatement.setObject(i + 1,args[i]); //处理？
}
int rows = preparedStatement.executeUpdate(); //影响行数
return rows;
}finally {
JDBCUtil.close(connection,preparedStatement); //无论是否有异常，都能够实现资源的关闭
}
}
}
@Test
public void test9(){
String sql = "insert into customer(name,gender,age,phone) values (?,?,?,?)";
try {
int rows = CommonUtil.update(sql, "小七","女",27,"77777777777"); //可变参数直接传入想要操作的数据
System.out.println(rows + " rows affect");
} catch (Exception e) {
e.printStackTrace();
}
}
在一段程序中插入两条数据，在以上程序中会连接两次数据库，造成资源的浪费，效率较低，在实际应用中，应避免connection的多次创建，这样也可以更好的支持事务性 *** 作。改进Commonutil，将connection作为参数传入update方法中，避免在一段程序中多次连接数据库。
//有了异常不要私自扣下，发送给调用者
public static int update(Connection connection,String sql,Object... args) throws SQLException, IOException, ClassNotFoundException {
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
for(int i = 0; i < args.length; i++){
preparedStatement.setObject(i + 1,args[i]);
}
int rows = preparedStatement.executeUpdate();
return rows;
}finally {
JDBCUtil.close(null,preparedStatement);
}
}
@Test
public void test10(){
String sql = "insert into customer(name,gender,age,phone) values (?,?,?,?)";
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
int rows = 0;
rows = CommonUtil.update(connection, sql,"小七1","女",27,"77777777777");
System.out.println(rows + " rows affect");
rows = CommonUtil.update(connection, sql,"小七2","女",27,"77777777777");
System.out.println(rows + " rows affect");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection);
}
}
4、JDBC的API ResultSet
使用ResultSet执行对数据库的查询 *** 作
@Test
public void test(){
String sql = "select id,name,gender,age,phone from customer where id > ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);
resultSet = preparedStatement.executeQuery(); //结果集对象中内部的游标指向第一条数据之前
while(resultSet.next()){ //移动游标到下一行
int id = resultSet.getInt(1); //指向当前游标的第一列
String name = resultSet.getString(2);
String gender = resultSet.getString(3);
int age = resultSet.getInt(4);
String phone = resultSet.getString(5);
System.out.println(id + "t" + name + "t" + gender + "t" + age + "t" + phone);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement,resultSet);
}
}
public class JDBCUtil {
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
if(resultSet != null){
try{
resultSet.close();
}catch (Exception e){
e.printStackTrace();
}
}
if(preparedStatement != null){
try{
preparedStatement.close();
}catch (Exception e){
e.printStackTrace();
}
}
if(connection != null){
try{
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
使用列标签获取每一列的值，列标签是指虚表的列名，也就是别名。
while(resultSet.next()){ //移动游标到下一行
int id = resultSet.getInt("id"); //指向当前游标的第一列
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
int age = resultSet.getInt("age");
String phone = resultSet.getString("phone");
System.out.println(id + "t" + name + "t" + gender + "t" + age + "t" + phone);
}
给打印出来的所有数据添加表头（添加列名），并实现动态获取数据
@Test
public void test2(){
String sql = "select id,name,gender,age,phone from customer where id > ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);
resultSet = preparedStatement.executeQuery(); //结果集对象中内部的游标指向第一条数据之前
ResultSetmetaData metaData = resultSet.getmetaData(); //获取虚表表结构
int columnCount = metaData.getColumnCount(); //获取虚表的列数
//打印表头
for(int i = 0; i < columnCount; i++){
String label = metaData.getColumnLabel(i + 1); //获取列标签
System.out.print(label + "t");
}
System.out.println();
//打印数据
while(resultSet.next()){ //移动游标到下一行
for(int i = 0; i < columnCount; i++){
String columnLabel = metaData.getColumnLabel(i + 1); //获取列标签
Object value = resultSet.getObject(columnLabel); //根据动态获取的列标签获取对应的数据值
System.out.print(value + "t");
}
System.out.println();
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement,resultSet);
}
}
将结果集对象化，需要提供相应的Customer类，此过程称为ORMapping，Object Relation DB Mapping。
public class Customer {
private int id;
private String name;
private String gender;
private int age;
private String phone;
public Customer() {
}
public Customer(int id, String name, String gender, int age, String phone) {
this.id = id;
this.name = name;
this.gender = gender;
this.age = age;
this.phone = phone;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + ''' +
", gender='" + gender + ''' +
", age=" + age +
", phone='" + phone + ''' +
'}';
}
}
@Test
public void test3(){
ArrayList list = new ArrayList<>();
String sql = "select id,name,gender,age,phone from customer where id > ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);
resultSet = preparedStatement.executeQuery(); //结果集对象中内部的游标指向第一条数据之前
ResultSetmetaData metaData = resultSet.getmetaData(); //获取虚表表结构
int columnCount = metaData.getColumnCount(); //获取虚表的列数
//打印数据
while(resultSet.next()){ //移动游标到下一行
Customer customer = new Customer();
for(int i = 0; i < columnCount; i++){
String columnLabel = metaData.getColumnLabel(i + 1); //获取到的是列标签，同时也是属性名
Object value = resultSet.getObject(columnLabel); //根据动态获取的标签获取对应的数据值
Field field = Customer.class.getDeclaredField(columnLabel);//根据列标签获取到属性的定义
field.setAccessible(true);
field.set(customer,value);
}
list.add(customer);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement,resultSet);
}
for(Customer customer : list){
System.out.println(customer);
}
}
练习：查询Teacher表，并将结果对象化。
public class Teacher {
private int id;
private String name;
private String phone;
private String gender;
private int age;
private String address;
public Teacher() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Teacher(int id, String name, String phone, String gender, int age, String address) {
this.id = id;
this.name = name;
this.phone = phone;
this.gender = gender;
this.age = age;
this.address = address;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + ''' +
", phone='" + phone + ''' +
", gender='" + gender + ''' +
", age=" + age +
", address='" + address + ''' +
'}';
}
}
@Test
public void test4(){
ArrayList list = new ArrayList<>();
String sql = "select id,name,gender,age,phone from teacher where id > ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);
resultSet = preparedStatement.executeQuery(); //结果集对象中内部的游标指向第一条数据之前
ResultSetmetaData metaData = resultSet.getmetaData(); //获取虚表表结构
int columnCount = metaData.getColumnCount(); //获取虚表的列数
//打印数据
while(resultSet.next()){ //移动游标到下一行
Teacher teacher = new Teacher();
for(int i = 0; i < columnCount; i++){
String columnLabel = metaData.getColumnLabel(i + 1); //获取到的是列标签，同时也是属性名
Object value = resultSet.getObject(columnLabel); //根据动态获取的标签获取对应的数据值
Field field = Teacher.class.getDeclaredField(columnLabel);//根据列标签获取到属性的定义
field.setAccessible(true);
field.set(teacher,value);
}
list.add(teacher);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement,resultSet);
}
for(Teacher teacher : list){
System.out.println(teacher);
}
}
@Test
public void test1(){
String sql = "select id,name,gender,age,phone from customer where id > ?";
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);
resultSet = preparedStatement.executeQuery(); //结果集对象中内部的游标指向第一条数据之前
while(resultSet.next()){ //移动游标到下一行
int id = resultSet.getInt("id"); //指向当前游标的第一列
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
int age = resultSet.getInt("age");
String phone = resultSet.getString("phone");
System.out.println(id + "t" + name + "t" + gender + "t" + age + "t" + phone);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement,resultSet);
}
}
将公共的方法抽取出去，提示：类模板，泛型。
public static List getList(Connection connection, Class clazz, String sql, Object... args) throws Exception{
ArrayList list = new ArrayList<>();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
//替换sql中的？
for(int i = 0; i < args.length; i++){
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery(); //结果集对象中内部的游标指向第一条数据之前
ResultSetmetaData metaData = resultSet.getmetaData(); //获取虚表表结构
int columnCount = metaData.getColumnCount(); //获取虚表的列数
//打印数据
while(resultSet.next()){ //移动游标到下一行
T instance = clazz.newInstance();
for(int i = 0; i < columnCount; i++){
String columnLabel = metaData.getColumnLabel(i + 1); //获取到的是列标签，同时也是属性名
Object value = resultSet.getObject(columnLabel); //根据动态获取的标签获取对应的数据值
Field field = clazz.getDeclaredField(columnLabel);//根据列标签获取到属性的定义
field.setAccessible(true); //暴力反射
field.set(instance,value); //通过反射的方式为目标对象的属性赋值
}
list.add(instance);
}
return list;
}finally {
JDBCUtil.close(null,preparedStatement,resultSet);
}
}
@Test
public void test6() throws Exception{
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
List list = CommonUtil.getList(connection,Teacher.class, "select * from teacher where id > ?", 1);
for(Teacher teacher : list){
System.out.println(teacher);
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(connection);
}
}
5、JDBC的事务处理
事务执行流程：关闭自动提交 -- 执行若干DML -- 成功commit，失败rollback -- 还原设置。
public static void main(String[] args){
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
List list = CommonUtil.getList(connection, Teacher.class, "select * from teacher");
for(Teacher teacher : list){
System.out.println(teacher);
}
System.out.println("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&");
//1)关闭自动提交
connection.setAutoCommit(false);
//2)执行若干DML
int rows = CommonUtil.update(connection, "delete from teacher");
System.out.println(rows + " rows affected");
list = CommonUtil.getList(connection, Teacher.class, "select * from teacher");
for(Teacher teacher : list){
System.out.println(teacher);
}
//String string = null;
//System.out.println(string.length());
//3)成功commit
connection.commit();
}catch (Exception e){
e.printStackTrace();
//4)失败rollback
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
//还原设置
try {
connection.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
JDBCUtil.close(connection);
}
}
6、数据库连接池
之间的所有 *** 作每进行一次都会获取一次连接，造成效率较低。为了解决频繁进行数据库连接造成的低效率，使用连接池提前将数据库连接好，放在池子中。当用户请求时，将此资源分配给用户，用户使用后，归还到池子中即可。
数据库连接池的工作原理：
数据库连接池驱动程序包-MySQL文档类资源-CSDN下载
使用Druid驱动连接数据库
@Test
public void test() throws SQLException {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/jdbc");
druidDataSource.setUsername("root");
druidDataSource.setPassword("hike");
Connection connection = druidDataSource.getConnection(); //代理对象
System.out.println(connection);
System.out.println(connection.getClass()); //获取到的是一个被包装过的对象
connection.close(); //只是把连接归还给连接池
}
通过配置文件获取连接
编写配置文件Druid.properties
driverClassName = com.mysql.jdbc.Driver url = jdbc:mysql://127.0.0.1:3306/jdbc username = root password = hike
@Test
public void test1() throws Exception{
InputStream inputStream = getClass().getClassLoader().getResourceAsStream("Druid.properties");
Properties properties = new Properties();
properties.load(inputStream);
assert inputStream != null;
inputStream.close();
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
其他一些设置
druidDataSource.setInitialSize(5); //初始容量
druidDataSource.setMaxActive(40); //最大激活数
druidDataSource.setMinIdle(12); //最小空闲数
在JDBCUtil中将连接抽取为公共方法
//只需要一个连接池就可以了
private static DataSource dataSource;
static { //类加载时就把池子建好
InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("Druid.properties");
Properties properties = new Properties();
try {
properties.load(inputStream);
inputStream.close();
dataSource = DruidDataSourceFactory.createDataSource(properties);
}catch (Exception e){
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
Connection connection = dataSource.getConnection();
return connection;
}
测试：
@Test
public void test2() throws Exception {
Connection connection = JDBCUtil.getConnection();
System.out.println(connection);
}
7、DBUtil 工具类
dbutils.zip-MySQL文档类资源-CSDN下载
使用DBUtils插入数据
@Test
public void test() throws Exception {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
int rows = queryRunner.update(connection, "insert into user(username,password) values (?,?)", "admin1", "admin1");
System.out.println(rows + "rows affected");
}
查询数据
@Test
public void test1() throws Exception {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select id,name from customer where id > ?";
//结果集处理器，将结果集中的所有记录映射成Javabean对象，并放入list集合中
BeanListHandler beanHandler = new BeanListHandler<>(Customer.class);
List list = queryRunner.query(connection, sql, beanHandler, 1);
for(Customer customer : list) {
System.out.println(customer);
}
}
标量处理器
@Test
public void test2() throws Exception {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select id,name from customer where id > ?";
//只取结果集中的第一行第一列
ScalarHandler scalarHandler = new ScalarHandler();
Object query = queryRunner.query(connection, sql, scalarHandler, 1);
System.out.println(query);
}
8、DAO层编写逻辑
编写TeacherDAO
DAO层专门管理数据库表与java对象之间的映射关系，data access object（DAO）。
public class TeacherDAO {
private QueryRunner queryRunner = new QueryRunner();
//获取批量对象
public List getList(String sql,Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.query(connection, sql, new BeanListHandler(Teacher.class), args);
}finally {
JDBCUtil.close(connection);
}
}
//获取一个对象
public Teacher getBean(String sql,Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.query(connection, sql, new BeanHandler(Teacher.class), args);
}finally {
JDBCUtil.close(connection);
}
}
//获取某一个值
public Object getValue(String sql,Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.query(connection, sql, new ScalarHandler(), args);
}finally {
JDBCUtil.close(connection);
}
}
//通用更新 *** 作
public int updata(String sql,Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.update(connection, sql, args);
}finally {
JDBCUtil.close(connection);
}
}
@Test
public void test(){
TeacherDAO teacherDAO = new TeacherDAO();
try {
List list = teacherDAO.getList("select * from teacher");
for(Teacher teacher : list){
System.out.println(teacher);
}
} catch (Exception e) {
e.printStackTrace();
}
}
编写CustomerDAO
private QueryRunner queryRunner = new QueryRunner();
//获取批量对象
public List getList(String sql, Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.query(connection, sql, new BeanListHandler(Customer.class), args);
}finally {
JDBCUtil.close(connection);
}
}
//获取一个对象
public Customer getBean(String sql,Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.query(connection, sql, new BeanHandler(Customer.class), args);
}finally {
JDBCUtil.close(connection);
}
}
//获取某一个值
public Object getValue(String sql,Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.query(connection, sql, new ScalarHandler(), args);
}finally {
JDBCUtil.close(connection);
}
}
//通用更新 *** 作
public int updata(String sql,Object... args) throws Exception {
Connection connection = null;
try {
connection = JDBCUtil.getConnection();
return queryRunner.update(connection, sql, args);
}finally {
JDBCUtil.close(connection);
}
}
@Test
public void test1(){
CustomerDAO customerDAO = new CustomerDAO();
try {
List list = customerDAO.getList("select * from customer");
for(Customer customer : list){
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
}
}
将CustomerDAO与TeacherDAO公共的部分抽取为JDBCDAO（泛型，类模板）。
public class JDBCDAO{ private QueryRunner queryRunner = new QueryRunner(); private Class clazz; public JDBCDAO(Class clazz){ this.clazz = clazz; } //获取批量对象 public List getList(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JDBCUtil.getConnection(); return queryRunner.query(connection, sql, new BeanListHandler (clazz), args); } finally { JDBCUtil.close(connection); } } //获取一个对象 public T getBean(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JDBCUtil.getConnection(); return queryRunner.query(connection, sql, new BeanHandler (clazz), args); } finally { JDBCUtil.close(connection); } } //获取某一个值 public Object getValue(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JDBCUtil.getConnection(); return queryRunner.query(connection, sql, new ScalarHandler(), args); } finally { JDBCUtil.close(connection); } } //通用更新 *** 作 public int updata(String sql, Object... args) throws Exception { Connection connection = null; try { connection = JDBCUtil.getConnection(); return queryRunner.update(connection, sql, args); } finally { JDBCUtil.close(connection); } } } public class CustomerDAO extends JDBCDAO { public CustomerDAO() { super(Customer.class); } } public class TeacherDAO extends JDBCDAO { public TeacherDAO() { super(Teacher.class); } }
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）