以book书城，book数据库，t_user表格为例做记录。
一共是这些包：
1. 在bean包里面新建user.java 封装好user对象
package bean;
public class user {
private int id;
private String username;
private String password;
private String email;
public user() {
// TODO Auto-generated constructor stub
}
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;
}
@Override
public String toString() {
return "user [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email + "]";
}
public user(int id, String username, String password, String email) {
super();
this.id = id;
this.username = username;
this.password = password;
this.email = email;
}
}
2. 在Utils包里新建JdbcUtils.java，这是一个操作数据库的工具类，方便调用，简化代码量。【里面还有C3P0，DBCP，Druid连接池等方式建立的连接，一起写进去了，是一个集合文件】
package Utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import JdbcConnection.connectionBest;
//这是一个操作数据库的工具类，方便调用，简化代码量
public class JdbcUtils {
public static Connection getConnection() throws Exception { //连接数据库
Connection conn=null;
//1.读取配置文件中的四个基本信息
InputStream is = connectionBest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String username=pros.getProperty("username");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driverClassName=pros.getProperty("driverClassName");
//2.加载驱动
Class.forName(driverClassName);
//3.获取连接
conn = (Connection) DriverManager.getConnection(url,username,password);
System.out.println(conn);
System.out.println("数据库连接成功");
return conn;
}
//增删改的关闭资源
public static void closeResourse(Connection conn,java.sql.PreparedStatement ps) { //关闭资源(连接和PreparedStatement)
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//查询的关闭资源
public static void closeResourse(Connection conn,java.sql.PreparedStatement ps,ResultSet rs) {
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(rs!=null)
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//用DBUtils工具类来关闭资源(*简介版)
public static void closeResourse1(Connection conn,java.sql.PreparedStatement ps,ResultSet rs) {
DbUtils.closeQuietly(conn);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(rs);
}
//*****用C3P0连接池方法来获取连接****
private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection1() throws SQLException {
Connection conn = (Connection) cpds.getConnection();
return conn;
}
//******用DBCP连接池方式来获取链接*******
private static DataSource source;
static {
try {
Properties pros = new Properties();
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
pros.load(is);
source = BasicDataSourceFactory.createDataSource(pros);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection2() throws Exception {
Connection conn = source.getConnection();
return conn;
}
//******用Druid连接池方式来获取连接*******
private static DataSource source2;
static {
try {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
source2=DruidDataSourceFactory.createDataSource(pros);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection3() throws Exception {
Connection conn = source2.getConnection();
return conn;
}
}
3.然后分别写一下不同连接方式的配置文件：
- jdbc.properties
username=root
password=217608wjy
url=jdbc:mysql://localhost:3306/book
driverClassName=com.mysql.jdbc.Driver
initialSize=5
maxActive=10
- dbcp.properties
DriverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/book?useSSL=false
username=root
password=217608wjy
- druid.properties
username=root
password=217608wjy
url=jdbc:mysql://localhost:3306/book
driverClassName=com.mysql.jdbc.Driver
initialSize=5
maxActive=10
- c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="helloc3p0">
<!-- 提供获取连接的4个基本信息 -->
<property name="user">root</property>
<property name="password">217608wjy</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/book?useSSL=false</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">10</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">100</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">10</property>
<!-- c3p0数据库连接池最多维护的Statement的个数 -->
<property name="maxStatements">50</property>
<!-- 每个连接中最多使用的Statement的个数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
4.写一下三种数据库连接池的测试文件：
- C3P0Test.java
package ConnectionPool;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Test {
//方式一：
@Test
public void testGetConnection() throws Exception {
//获取c3p0数据库连接池
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/book");
cpds.setUser("root");
cpds.setPassword("217608wjy");
//通过设置相关的参数，对数据库连接池进行管理
//设置初识时数据库连接池中的连接数
cpds.setInitialPoolSize(10);
Connection conn = cpds.getConnection();
System.out.println(conn);
}
//方式二：使用xml配置文件（*推荐使用*）
@Test
public void testGetConnectionBetter() throws SQLException {
ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
Connection conn = cpds.getConnection();
System.out.println(conn);
}
}
- DBCPTest.java
package JdbcConnection;
import java.io.InputStream;
import java.sql.DriverManager;
import java.util.Properties;
import org.junit.Test;
import com.mysql.jdbc.Connection;
public class connectionBest{
@Test
public void Connection5() throws Exception {
//1.读取配置文件中的四个基本信息
InputStream is = connectionBest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String username=pros.getProperty("username");
String password=pros.getProperty("password");
String url=pros.getProperty("url");
String driverClassName=pros.getProperty("driverClassName");
//2.加载驱动
Class.forName(driverClassName);
//3.获取连接
Connection conn = (Connection) DriverManager.getConnection(url,username,password);
System.out.println(conn);
System.out.println("数据库连接成功");
}
}
- DruidTest.java
package ConnectionPool;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import org.junit.Test;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidTest {
//使用配置文件
@Test
public void getConnection() throws Exception {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
DataSource source = DruidDataSourceFactory.createDataSource(pros);
Connection conn = source.getConnection();
System.out.println(conn);
}
}
*5.来到DAO层：
第一步：在Dao包里写BaseDao（增删改查的方法）*【BaseDao.java】
package DAO;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import Utils.JdbcUtils;
public abstract class BaseDao {
//一：通用的【增删改】(已考虑事务）
public int update(Connection conn,String sql,Object ...args) { //把conn写在外面，而不是在里面造了
PreparedStatement ps=null;
try {
//1.预编译sql语句，返回PreparedStatement的实例
ps=(PreparedStatement) conn.prepareStatement(sql);
//2.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
//3.执行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//4.资源的关闭
JdbcUtils.closeResourse(null, ps);//conn也不关
}
return 0;
}
//二：针对不同表的【通用查询】操作，返回的是表中的【一条】记录（已考虑事务）
public <T> T queryForAll1(Connection conn,Class<T> clazz,String sql,Object ...args) { //范型方法T
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = (PreparedStatement) conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData来获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
@SuppressWarnings("deprecation")
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columValue = rs.getObject(i+1); //获取到了字段的值
String columnName = rsmd.getColumnName(i+1);//获取到了每个列的列名
//给user对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(null, ps, rs);
}
return null;
}
//三：针对不同表的【通用查询】操作，返回的是表中的【多条】记录(已考虑事务)
public <T> List<T> queryForAll2(Connection conn,Class<T> clazz,String sql,Object ...args) { //范型方法T
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = (PreparedStatement) conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据:ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData来获取结果集中的列数
int columnCount = rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list = new ArrayList<T>();
while (rs.next()) {
@SuppressWarnings("deprecation")
T t = clazz.newInstance();//创建t对象
for (int i = 0; i < columnCount; i++) { //处理结果集一行数据中的每一个列：给t对象指定的属性赋值
Object columValue = rs.getObject(i+1); //获取到了字段的值
String columnName = rsmd.getColumnName(i+1);//获取到了每个列的列名
//给user对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);// 把t添加到集合中
}
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(null, ps, rs);
}
return null;
}
//四.用于查询特殊值的通用方法(已考虑事务)
public <E>E getValue(Connection conn,String sql,Object...args) {
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = (PreparedStatement) conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs=ps.executeQuery();
if (rs.next()) {
return (E) rs.getObject(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(null, ps, rs);
}
return null;
}
}
第二步：在Dao包里写UserDAO接口（此接口用于规范对于user表的常用操作）【UserDAO.java】
package DAO;
import java.util.List;
import java.sql.Connection;
import bean.user;
//此接口用于规范对于user表的常用操作
public interface UserDAO {
//1.将user对象添加到数据库中
void insert(Connection conn,user user);
//2.通过指定的id，删除表中的一条记录
void deleteById(Connection conn,int id);
//3.通过内存中的user对象，去修改表中指定的记录
void update(Connection conn,user user);
//4.针对指定的id，查询得到对于的Customer对象
user getCustomerById(Connection conn,int id);
//5.查询表中的所有记录构成的集合
List<user> getAll(Connection conn);
//6.返回数据表中数据的条目数
Long getCount(Connection conn);
}
第三步：在Dao包里写UserDAOImpl（UserDAOImpl是UserDAO接口的【实现类】）【UserDAOImpl.java】
package DAO;
import java.util.List;
import java.sql.Connection;
import bean.user;
//UserDAOImpl是UserDAO接口的【实现类】
public class UserDAOImpl extends BaseDao implements UserDAO {
@Override
public void insert(Connection conn, user user) {
String sql="insert into t_user(username,password,email) values(?,?,?)";
update(conn, sql, user.getUsername(),user.getPassword(),user.getEmail());
}
@Override
public void deleteById(Connection conn, int id) {
String sql="delete from t_user where id=?";
update(conn, sql, id);
}
@Override
public void update(Connection conn, user user) {
String sql="update t_user set username=?,password=?,email=? where id=?";
update(conn, sql, user.getUsername(),user.getPassword(),user.getEmail(),user.getId());
}
@Override
public user getCustomerById(Connection conn, int id) {
String sql="select id,username,password,email from t_user where id=?";
user user = queryForAll1(conn, user.class, sql, id);
return user;
}
@Override
public List<user> getAll(Connection conn) {
String sql="select id,username,password from t_user";
List<user> list = queryForAll2(conn, user.class, sql);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql="select count(*) from t_user";
return getValue(conn, sql);
}
}
*6.来到JUnit里写整体测试，进行数据的增删查改：【UserDAOImplTest.java】
package JUnit;
import java.util.List;
import org.junit.Test;
import java.sql.Connection;
import DAO.UserDAOImpl;
import Utils.JdbcUtils;
import bean.user;
public class UserDAOImplTest {
private UserDAOImpl dao=new UserDAOImpl();
@Test
public void testInsert() {
Connection conn=null;
try {
conn = JdbcUtils.getConnection();
user user = new user(15, "小圆", "123", "123@qq.com");
dao.insert(conn, user);
System.out.println("添加数据成功～");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
@Test
public void testDeleteById() {
Connection conn=null;
try {
conn = JdbcUtils.getConnection();
dao.deleteById(conn, 5);
System.out.println("删除数据成功～");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
@Test
public void testUpdateConnectionUser() {
Connection conn=null;
try {
conn = JdbcUtils.getConnection();
user user = new user(3, "小柴", "69", "69@qq.com");
dao.update(conn, user);
System.out.println("数据修改成功～");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
@Test
public void testGetUserById() {
Connection conn=null;
try {
conn = JdbcUtils.getConnection();
user user = dao.getCustomerById(conn, 1);
System.out.println(user);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
@Test
public void testGetAll() {
Connection conn=null;
try {
conn = JdbcUtils.getConnection();
List<user> list = dao.getAll(conn);
list.forEach(System.out::println);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
@Test
public void testGetCount() {
Connection conn=null;
try {
conn = JdbcUtils.getConnection();
Long count = dao.getCount(conn);
System.out.println("表中的记录数为："+count);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
}
*7.另外还有一个用QueryRunner（DBUtilis）的测试：
package DBUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import Utils.JdbcUtils;
import bean.user;
public class QueryRunnerTest {
//1.测试用QueryRunner（DBUtilis）添加数据
@Test
public void testInsert() throws Exception {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn=JdbcUtils.getConnection3();
String sql="insert into t_user(username,password,email) values(?,?,?)";
int insertCount = runner.update(conn, sql, "小薇","530","530@qq.com");
System.out.println("成功添加了"+insertCount+"条记录！");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
JdbcUtils.closeResourse(conn, null);
}
}
//2.BeanHander:是ResultSetHander接口的实现类，用于封装表中的【一条】记录
@Test
public void testQuery1() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
String sql="select id,username,password,email from t_user where id=?";
BeanHandler<user> handler = new BeanHandler<>(user.class);
conn=JdbcUtils.getConnection3();
user user = runner.query(conn, sql, handler,3);
System.out.println(user);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
//2.BeanHander:是ResultSetHander接口的实现类，用于封装表中的【多条】记录
@Test
public void testQuery2() {
Connection conn=null;
try {
QueryRunner runner = new QueryRunner();
String sql="select id,username,password,email from t_user where id<?";
BeanListHandler<user> handler = new BeanListHandler<>(user.class);
conn=JdbcUtils.getConnection3();
List<user> list = runner.query(conn, sql, handler,10);
list.forEach(System.out::println);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
//3.MapHandler:是ResultSetHandler接口的实现类，对应表中的一条记录。
//（将字段以及相应字段的值作为map中的key和value）
@Test
public void testQuery3() {
Connection conn=null;
try {
QueryRunner runner = new QueryRunner();
String sql="select id,username,password,email from t_user where id=?";
MapHandler handler = new MapHandler();
conn=JdbcUtils.getConnection3();
Map<String, Object> map = runner.query(conn, sql, handler,7);
System.out.println(map);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
//4.MapListHandler:是ResultSetHandler接口的实现类，对应表中的【多条】记录。
//（将字段以及相应字段的值作为map中的key和value，将这些map添加到list中）
@Test
public void testQuery4() {
Connection conn=null;
try {
QueryRunner runner = new QueryRunner();
String sql="select id,username,password,email from t_user where id<?";
MapListHandler handler = new MapListHandler();
conn=JdbcUtils.getConnection3();
List<Map<String, Object>> list = runner.query(conn, sql, handler,10);
list.forEach(System.out::println);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
//5.查询数据库中一共有多少条数据
@Test
public void testQuery5() {
Connection conn=null;
try {
QueryRunner runner = new QueryRunner();
conn=JdbcUtils.getConnection3();
String sql="select count(*)from t_user";
ScalarHandler handler = new ScalarHandler();
Long count = (Long) runner.query(conn, sql, handler);
System.out.println("一共有"+count+"条记录！");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtils.closeResourse(conn, null);
}
}
}
综上！！！整理完毕。希望对记性不好的自己和有缘翻到这篇巨巨巨长记录jdbc连接数据库的你们，能有一点点帮助。copy去8！！
扫一扫
3358
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
