Java后端开发——JDBC组件

组件"/>

Java后端开发——JDBC组件

JDBC（Java Database Connectivity）是Java SE平台的一种标准API，它提供了一种标准的方法来访问关系型数据库，使得Java程序能够与各种不同的数据库进行交互，这篇文章我们来进行实验体验一下。

自定义JDBC连接工具类
1.编写第一代JDBCUtils并测试查看结果
第一代JDBCUtils：

package com.javaweb.utils;
import java.io.FileReader;
import java.sql.*;
import java.util.Properties;public class JDBCUtils2 {private static String driver;private static String url;private static String user;private static String password;
static {try {// 1. 创建Properties集合类。Properties properties = new Properties();// 2. 加载文件properties.load(new FileReader(JDBCUtils.class.getClassLoader().getResource("jdbc.properties").getPath()));driver = properties.getProperty("driver");url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");// 注册驱动Class.forName(driver);} catch (Exception e) {e.printStackTrace();}}
public static Connection getConnection() throws SQLException { return   DriverManager.getConnection(url, user, password);}// 释放资源public static void close(ResultSet rs, PreparedStatement pstm, Connection conn) {try {if (rs != null) {rs.close();}if (pstm != null) {pstm.close();}if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}
}

第一代JDBCUtils测试:

package com.javaweb.utils;import java.sql.*;public class testJdbcUtils1 {public static void main(String[] args) throws Exception {Connection conn = JDBCUtils.getconnection();String sql="update account set money = ? where id = ?";PreparedStatement pstm = conn.prepareStatement(sql);pstm.setString(1, "20000");pstm.setString(2, "1");int count=pstm.executeUpdate();JDBCUtils.close( null,pstm, conn);System.out.println(count);}
}

2.编写第二代JDBCUtils并测试查看结果
在src下创建数据库连接配置文件：jdbc.properties

第二代JDBCUtils代码：

package com.javaweb.utils;
import java.io.FileReader;
import java.sql.*;
import java.util.Properties;public class JDBCUtils2 {private static String driver;private static String url;private static String user;private static String password;
static {try {// 1. 创建Properties集合类。Properties properties = new Properties();// 2. 加载文件properties.load(new FileReader(JDBCUtils.class.getClassLoader().getResource("jdbc.properties").getPath()));driver = properties.getProperty("driver");url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");// 注册驱动Class.forName(driver);} catch (Exception e) {e.printStackTrace();}}
public static Connection getConnection() throws SQLException { return   DriverManager.getConnection(url, user, password);}// 释放资源public static void close(ResultSet rs, PreparedStatement pstm, Connection conn) {try {if (rs != null) {rs.close();}if (pstm != null) {pstm.close();}if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}
}

第二代JDBCUtils测试：

package com.javaweb.utils;
import java.sql.*;
public class testJdbcUtils2 {public static void main(String[] args) throws Exception {Connection conn = JDBCUtils2.getConnection();String sql = "update account set money = ? where id = ?";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setDouble(1, 50000);pstmt.setInt(2, 1);int count = pstmt.executeUpdate();System.out.println(count);}
}

封装增删改查DAO
1.搭建数据库环境，创建库tb_user,插入测试记录
在MySQL中创建一个名称为db的数据库，然后在该数据库中创建一个tb_user表，再向表中插入3条数据。

create database db charset=utf8;
CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(40),password VARCHAR(40),email VARCHAR(60),birthday DATE 
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO tb_user(username,password ,email,birthday) VALUES('zs','123456','zs@sina','1980-12-04');
INSERT INTO tb_user(username,password ,email,birthday) VALUES('lisi','123456','lisi@sina','1981-12-04');
INSERT INTO tb_user(username,password ,email,birthday) VALUES('wangwu','123456','wangwu@sina','1979-12-04');

2.创建JavaBean：User.java
在src目录下，新建一个名称为chapter02.jdbc.example的包
创建JavaBean，User类的具体实现方式如下：

package com.javaweb.bean;import java.util.Date;public class user {private int id;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}private String username;private String password;private String email;private Date birthday;}

创建数据库访问层DAO类封装增删改查代码：UserDao.java

package chapter02.jdbc.example;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;import com.javaweb.bean.user;public class UsersDao {//添加用户的操作public boolean insert(user user) {Connection conn = null;Statement stmt=null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();stmt = conn.createStatement();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String birthday = sdf.format(user.getBirthday());String sql = "INSERT INTO tb_user(id,username,password,email,birthday)"+"VALUES("+user.getId()+",'"+user.getUsername()+"','"+user.getPassword()+"','"+user.getEmail()+"','"+birthday + "')";System.out.println(sql);int num = stmt.executeUpdate(sql);if (num > 0) {return true;}return false;} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(rs,stmt,conn);}return false;}public ArrayList<user> findAll(){Connection conn = null;Statement stmt=null;ResultSet rs = null;ArrayList<user> list = new ArrayList<user>();try {conn = JDBCUtils.getConnection();stmt = conn.createStatement();String sql = "SELECT * FROM tb_user";rs = stmt.executeQuery(sql);while(rs.next()) {user user = new user();user.setId(rs.getInt("id"));user.setUsername(rs.getString("username"));user.setPassword(rs.getString("password"));user.setEmail(rs.getString("email"));user.setBirthday(rs.getDate("birthday"));list.add(user);}return list;} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(rs, stmt, conn);}return null;}public user find(int id) {Connection conn=null;Statement stmt=null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();stmt=conn.createStatement();String sql = "SELECT * FROM tb_user WHERE id=" + id;rs = stmt.executeQuery(sql);while(rs.next()) {user user = new user();user.setId(rs.getInt("id"));user.setUsername(rs.getString("username"));user.setPassword(rs.getString("password"));user.setEmail(rs.getString("email"));user.setBirthday(rs.getDate("birthday"));return user;}return null;} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(rs,stmt, conn);}return null;}public boolean delete(int id) {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();stmt= conn.createStatement();String sql = "DELETE FROM tb_user WHERE id=" + id;int num =stmt.executeUpdate(sql);if(num > 0) {return true;}return false;} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(rs, stmt,conn);}return false;}public boolean update(user user) {Connection conn =null;Statement stmt = null;ResultSet rs = null;try {conn= JDBCUtils.getConnection();stmt = conn.createStatement();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String birthday =sdf.format(user.getBirthday());String sql = "UPDATE tb_user set username='"+user.getUsername()+"',password='"+user.getPassword() + "',email='"+user.getEmail() + "',birthday='" + birthday+"'WHERE id=" + user.getId();int num = stmt.executeUpdate(sql);if (num > 0) {return true;}return false;} catch (Exception e) {e.printStackTrace();}finally {JDBCUtils.release(rs, stmt, conn);}return false;}
}

4.利用JUnit对DAO的方法进行单元测试，并查看结果
创建测试类JDBCTESTl，利用JUnit对DAO-insert方法添加tb_user表中的数据:

在这里插入代码片`package chapter02.jdbc.example;import java.util.Date;import com.javaweb.bean.user;@Test
public class JDBCTest {public static void main(String[] args) {//向users表插入一个用户信息UsersDao ud = new UsersDao();user user=new user();user.setId(4);user.setUsername("xiaoli");user.setPassword("00000");user.setEmail("h23526456@qq");user.setBirthday(new Date());boolean b= ud.insert(user);System.out.println(b);}}`

测试结果：

创建测试类JDBCTEST03，利用JUnit对DAO-findAll方法根据ID查询tb_user表中数据：

package chapter02.jdbc.example;import java.util.ArrayList;import com.javaweb.bean.user;public class FindAllUsersTest {public static void main(String[] args) {UsersDao usersDao = new UsersDao();ArrayList<user> list = usersDao.findAll();for (int i=0; i< list.size(); i++) {System.out.println("第" + (i+1) + "条数据的username的值为："+list.get(i).getUsername());}}}

测试结果，成功查询出tb_user表中所有username值：

创建测试类JDBCTEST03，利用JUnit对DAO-find方法根据ID查询tb_user表中数据：
我们根据ID查询tb_user表中ID为13的数据库信息：

package chapter02.jdbc.example;import static org.junit.Assert.*;import org.junit.Test;import com.javaweb.bean.user;public class JDBCTEST03 {@Testpublic void testFind() throws Exception {UsersDao dao= new UsersDao();user user=dao.find(13);System.out.println("id为13的User对象的name值为："+user.getUsername());}}

测试结果：
成功通过ID查询rb_user表中ID为13的username信息

创建测试类JDBCTEST04，利用JUnit对DAO-update方法方法根据ID更新tb_user表中数据：

package chapter02.jdbc.example;import java.util.Date;import org.junit.Test;import com.javaweb.bean.user;public class JDBCTEST04 {@Testpublic void testUpdate() throws Exception {UsersDao dao= new UsersDao();user user=new user();user.setId(13);user.setUsername("项秀才让");user.setPassword("abc");user.setEmail("123456@sina");user.setBirthday(new Date());boolean b=dao.update(user);System.out.println(b);}}

测试结果：
成功修改ID为13的数据库记录信息！

创建测试类JdbcTest01，利用DAO-delete方法删除tb_user表中的数据

package chapter02.jdbc.example;
import org.junit.jupiter.api.Test;
class JdbcTest01 {@Testpublic void testDelete() {UsersDao dao= new UsersDao();boolean b=dao.delete(8);System.out.println(b);}	
}

测试结果和sql查询结果：

用户登录程序
1.创建数据库表
在MySQL中使用一个名称为db_myweb的数据库，然后在该数据库中创建一个tb_user表，再向表中插入3条数据。

在这里插入代码片`Use db_myweb；
CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(40),password VARCHAR(40),email VARCHAR(60),birthday DATE 
)CHARACTER SET utf8 COLLATE utf8_general_ci;
插入数据：
INSERT INTO tb_user(username,password ,email,birthday) VALUES('zs','123456','zs@sina','1980-12-04');
INSERT INTO tb_user(username,password ,email,birthday) VALUES('lisi','123456','lisi@sina','1981-12-04');
INSERT INTO tb_user(username,password ,email,birthday) VALUES('wangwu','123456','wangwu@sina','1979-12-04');
`

2.编写登录页面
在web目录下创建一个名称为login的JSP文件，在该文件中添加用于用户登录时输入用户信息的表单元素。

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="/myweb/LoginServlet" method="post">
用户名：<input type="text" name="username">     <br>
密  码：<input type="password" name="password"> <br> 
<input type="submit" value="登录">
</form>
</body>
</html>

3.编写工具类
由于每次操作数据库时，都需要加载数据库驱动、建立数据库连接以及关闭数据库连接，为了避免代码的重复书写，所以需要建立一个专门用于操作数据库的工具类。在src下创建一个包com.javaweb.utils，在包中创建一个封装了上述操作的工具类JDBCUtils。

package com.javaweb.utils;import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;import com.mysql.jdbc.Connection;public class JDBCUtils {// 加载驱动static {try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {e.printStackTrace();}}// 获取连接public static Connection getconnection() throws Exception {String url = "jdbc:mysql://localhost:3306/db_myweb?useSSL=false";String user = "root";String password = "root";Connection conn = (Connection) DriverManager.getConnection(url, user, password);return conn;}// 释放资源public static void close(ResultSet rs, java.sql.PreparedStatement pstm, java.sql.Connection conn) {try {if (rs != null) {rs.close();}if (pstm != null) {pstm.close();}if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}}

4.UserDao增加方法按用户名和密码查询方法

// 根据username,password查找指定的user
public boolean findByUsernameAndPassword(String username,String password){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 发送SQL语句
String sql = "select * from tb_user where username=? and password=?";
// 获得PreparedStatement对象
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
rs = pstmt.executeQuery();
// 处理结果集
if (rs.next()) 
return true;
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, pstmt, conn);
}
return false;
}

5.实现登录功能的LoginServlet

package com.javaweb.servlet;import java.io.IOException;
import java.io.PrintWriter;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;/*** Servlet implementation class LoginServlet*/
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
/*** */private static final long serialVersionUID = 1L;protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {request.setCharacterEncoding("UTF-8");response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter();String username=request.getParameter("username");String password=request.getParameter("password");if(username!=null&&password!=null&&!username.equals("")&&!password.equals("")) {UserDao dao=new UserDao();boolean b=dao.findByUsernameAndPassword(username, password);if(b)out.print("欢迎登录！");elseout.print("用户名或密码不正确！");	}elseout.print("用户名或密码不能为空！");	}}

运行项目，查看效果





C3P0数据库连接池实验
1.导入JAR包c3p0-0.9.2.1.jar和mchange-commons-java-0.2.3.4.jar
在项目中导入JAR包c3p0-0.9.2.1.jar和mchange-commons-java-0.2.3.4.jar，并使用C3P0数据库连接池对象获取Connection对象。

2.创建C3P0Test测试用例类，测试代码

package com.javaweb.test;import java.sql.Connection;
import java.sql.SQLException;import org.junit.Test;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3P0Test {@Test
public void test() throws PropertyVetoException, SQLException, java.beans.PropertyVetoException {
ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/db_myweb?useSSL=false");
ds.setUser("root");
ds.setPassword("root");
ds.setInitialPoolSize(5);
ds.setMaxPoolSize(10);
Connection conn = ds.getConnection();
System.out.println(conn);
}}

测试结果：

3.在项目的src根目录下创建一个c3p0-config.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl"> jdbc:mysql://localhost:3306/db_myweb?useSSL=false</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="checkoutTimeout">30000</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxStatements">200</property>
</default-config>
</c3p0-config>

4.创建C3P0Test测试用例类，测试代码

package chapter02.jdbc.example;import java.beans.PropertyVetoException;
import java.sql.SQLException;import org.junit.Test;import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Connection;public class C3P0Test {@Test
public void testC3P0() throws SQLException {
ComboPooledDataSource ds= new ComboPooledDataSource();
System.out.println(ds.getConnection());
}
}

测试结果：
测试成功！

5.封装DataSourceUtils工具类

package com.javaweb.test;import java.sql.SQLException;import com.mysql.jdbc.Connection;public class DataSourceUtils {private static ComboPooledDataSource ds=new ComboPooledDataSource();public static ComboPooledDataSource getDataSource(){return  ds;}public static Connection getConnection() throws SQLException {return ds.getConnection();}
}

6.修改UserDao从DataSourceUtils工具类，获取数据库连接，并测试结果。

BUtils工具增删改查
1.将commons-dbutils-1.7.jar添加到项目的lib目录中

2.创建UserDaoQr.java，编写QueryRunner示例代码：增加、删除、修改

package cn.itcast.jdbc.dao;import java.sql.SQLException;
import java.util.List;import org.apachemons.dbutils.QueryRunner;
import org.apachemons.dbutils.handlers.BeanHandler;
import org.apachemons.dbutils.handlers.BeanListHandler;import cn.itcast.jdbc.javabean.User;
import cn.itcast.jdbc.utils.DataSourceUtils;public class UserDaoQr {
public boolean insert(User user) throws SQLException {
QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "INSERT INTO t_user(id,username,password) VALUES(null,?,?)";
int num= r.update(sql,user.getName(),user.getPassword());
if (num > 0) {
return true;
}
return false;
}
public boolean update(User user) throws SQLException {
QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "update t_user set username=?,password=?";
int num= r.update(sql,user.getName(),user.getPassword());
if (num > 0) {
return true;
}
return false;
}public boolean delete(int id) throws SQLException {
QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "delete from t_user where id=?";
int num= r.update(sql,id);
if (num > 0) {
return true;
}
return false;
}public User find(int id) throws SQLException{
QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "SELECT * FROM user WHERE id=?";
User user= r.query(sql, new BeanHandler<User>(User.class),id);
return user;
}
public List<User> findAll() throws SQLException{
QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "SELECT * FROM user";
List<User> list=r.query(sql, new BeanListHandler<User>(User.class));
return list;
}}

3.QueryRunner示例代码：单元测试增删改代码
单元测试增加数据：

package cn.itcast.jdbc.dao;
import java.sql.SQLException;import org.junit.Test;import cn.itcast.jdbc.javabean.User;public class test {
@Testpublic void testInsert() throws SQLException {UserDaoQr dao=new UserDaoQr();User user=new User();user.setName("svdviming");user.setPassword("dad2dadadadad3");boolean b=dao.insert(user);if(b)System.out.println("数据插入成功！");elseSystem.out.println("数据插入失败！");}
}单元测试删除数据：
@Test
public void testDelete() throws SQLException {
UserDaoQr dao=new UserDaoQr();
boolean b=dao.delete(3);
if(b)
System.out.println("数据删除成功！");
else
System.out.println("数据删除失败！");
}}

测试结果：
数据删除成功！

4.QueryRunner示例代码：查询单个，并测试

package cn.itcast.jdbc.dao;import java.sql.SQLException;import org.apachemons.dbutils.QueryRunner;
import org.apachemons.dbutils.handlers.BeanHandler;import cn.itcast.jdbc.javabean.User;
import cn.itcast.jdbc.utils.DataSourceUtils;public class UserDaoQr {public User find(int id) throws SQLException{QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());String sql = "SELECT * FROM user WHERE id=?";User user= r.query(sql, new BeanHandler<User>(User.class),id);return user;}}

测试ID为2的数据：

package cn.itcast.jdbc.dao;import java.sql.SQLException;import org.junit.Test;import cn.itcast.jdbc.javabean.User;public class test {@Testpublic void testFind() throws SQLException {UserDaoQr dao=new UserDaoQr();User user=dao.find(2);System.out.println("name="+user.getName());}}

测试结果：
成功查询ID=2的数据！

5.QueryRunner示例代码：查询全部，并测试

package cn.itcast.jdbc.dao;import java.sql.SQLException;
import java.util.List;import org.apachemons.dbutils.QueryRunner;
import org.apachemons.dbutils.handlers.BeanHandler;
import org.apachemons.dbutils.handlers.BeanListHandler;import cn.itcast.jdbc.javabean.User;
import cn.itcast.jdbc.utils.DataSourceUtils;public class UserDaoQr {
public List<User> findAll() throws SQLException{
QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "SELECT * FROM user";
List<User> list=r.query(sql, new BeanListHandler<User>(User.class));
return list;
}
}

测试结果：
成功通过 findAll测试查询表中数据信息。