首先,新建项目如下:
新建lib目录,导入jar包
- mysql-connector-java-5.1.37-bin.jar 是mysql数据库驱动,用于操作mysql数据库
- druid-1.1.10.jar 德鲁伊数据库连接池,用于连接数据库
- commons-dbutils-1.3.jar commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装。
还需要添加到路径才能使用
添加路径完成
分成3个包
- util:JDBCUtils包含数据库的连接和关闭
- bean:实体类
- dao:数据库的操作
JDBCUtils.java
可直接复用
package com.jdbcTest.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JBDCUtils {
private static DataSource source;
static{
try {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
source = DruidDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
Connection conn = source.getConnection();
return conn;
}
public static void closeResource(Connection conn, Statement ps, ResultSet rs){
DbUtils.closeQuietly(conn);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(rs);
}
public static void closeResource(Connection conn){
DbUtils.closeQuietly(conn);
}
}
数据库连接配置文件
druid.properties
url=jdbc:mysql://localhost:3306/test username=root password=root driverClassName=com.mysql.jdbc.Driverbean包
以User表为例
项目结构
User.java
package com.jdbcTest.bean;
public class User {
int id;
String name;
String password;
String address;
String phone;
public User() {
}
public User(int id, String name, String password, String address, String phone) {
this.id = id;
this.name = name;
this.password = password;
this.address = address;
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + ''' +
", password='" + password + ''' +
", address='" + address + ''' +
", phone='" + phone + ''' +
'}';
}
}
dao包
项目结构
BaseDAO.java
可复用
定义一个用来被继承的对数据库进行基本操作的Dao
package com.jdbcTest.dao; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.Connection; import java.sql.SQLException; import java.util.List; 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; public abstract class BaseDAO{ private QueryRunner queryRunner = new QueryRunner(); // 定义一个变量来接收泛型的类型 private Class type; // 获取T的Class对象,获取泛型的类型,泛型是在被子类继承时才确定 public BaseDAO() { // 获取子类的类型 Class clazz = this.getClass(); // 获取父类的类型 // getGenericSuperclass()用来获取当前类的父类的类型 // ParameterizedType表示的是带泛型的类型 ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass(); // 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型 // 这个方法会返回一个Type的数组 Type[] types = parameterizedType.getActualTypeArguments(); // 获取具体的泛型的类型· this.type = (Class ) types[0]; } public int update(Connection conn, String sql, Object... params) { int count = 0; try { count = queryRunner.update(conn, sql, params); } catch (SQLException e) { e.printStackTrace(); } return count; } public T getBean(Connection conn,String sql, Object... params) { T t = null; try { t = queryRunner.query(conn, sql, new BeanHandler (type), params); } catch (SQLException e) { e.printStackTrace(); } return t; } public List getBeanList(Connection conn, String sql, Object... params) { List list = null; try { list = queryRunner.query(conn, sql, new BeanListHandler (type), params); } catch (SQLException e) { e.printStackTrace(); } return list; } public Object getValue(Connection conn,String sql, Object... params) { Object count = null; try { // 调用queryRunner的query方法获取一个单一的值 count = queryRunner.query(conn, sql, new ScalarHandler(), params); } catch (SQLException e) { e.printStackTrace(); } return count; } }
UserDAO和UserDAOImpl成对出现
UserDAO.java
接口
package com.jdbcTest.dao;
import com.jdbcTest.bean.User;
import java.sql.Connection;
import java.util.List;
public interface UserDAO {
void insert(Connection conn, User user);
void deleteById(Connection conn, int id);
void update(Connection conn, User user);
User getUserById(Connection conn, int id);
List getAll(Connection conn);
Long getCount(Connection conn);
}
UserDAOImpl.java
与数据库交互的具体实现,在这里写sql语句
package com.jdbcTest.dao; import com.jdbcTest.bean.User; import java.sql.Connection; import java.util.List; public class UserDAOImpl extends BaseDAO测试implements UserDAO { @Override public void insert(Connection conn, User user) { String sql = "insert into user(name,password,address,phone) values (?,?,?,?)"; update(conn, sql, user.getName(), user.getPassword(), user.getAddress(), user.getPhone()); } @Override public void deleteById(Connection conn, int id) { String sql = "delete from user where id = ?"; update(conn, sql, id); } @Override public void update(Connection conn, User user) { String sql = "update user set name = ?, password = ?, address = ?, phone = ? where id = ?"; update(conn, sql, user.getName(), user.getPassword(), user.getAddress(), user.getPhone(), user.getId()); } @Override public User getUserById(Connection conn, int id) { String sql = "select id,name,password,address,phone from user where id = ?"; User user = getBean(conn, sql, id); return user; } @Override public List getAll(Connection conn) { String sql = "select id,name,password,address,phone from user"; List all = getBeanList(conn, sql); return all; } @Override public Long getCount(Connection conn) { String sql = "select count(*) from user"; Long value = (Long) getValue(conn, sql); return value; } }
原User表数据
@Test
public void testInsertUser() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
userDAO.insert(conn, new User(0, "小红", "123456", "安徽", "13345672134"));
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testDeleteUser() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
userDAO.deleteById(conn, 8);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testUpdateUser() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
userDAO.update(conn, new User(1, "小绿", "123456", "安徽", "13345672134"));
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testSelect() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
User user = userDAO.getUserById(conn, 3);
System.out.println("user = " + user);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testSelectAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
List all = userDAO.getAll(conn);
all.forEach(System.out::println);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testCount() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Long count = userDAO.getCount(conn);
System.out.println("count = " + count);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
MyTest.java
package com.jdbcTest;
import com.jdbcTest.bean.User;
import com.jdbcTest.dao.UserDAOImpl;
import com.jdbcTest.util.JDBCUtils;
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class MyTest {
UserDAOImpl userDAO = new UserDAOImpl();
@Test
public void testInsertUser() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
userDAO.insert(conn, new User(0, "小红", "123456", "安徽", "13345672134"));
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testDeleteUser() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
userDAO.deleteById(conn, 8);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testUpdateUser() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
userDAO.update(conn, new User(1, "小绿", "123456", "安徽", "13345672134"));
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testSelect() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
User user = userDAO.getUserById(conn, 3);
System.out.println("user = " + user);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testSelectAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
List all = userDAO.getAll(conn);
all.forEach(System.out::println);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
@Test
public void testCount() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Long count = userDAO.getCount(conn);
System.out.println("count = " + count);
} catch(SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn);
}
}
}
总结
完整项目结构
只需要按需要查询的表,增加以上圈出的3个java文件即可实现对数据库表的操作。
改进:实现多表连接查询和嵌套查询。
参考资料尚硅谷JDBC核心技术视频教程(康师傅带你一站式搞定jdbc)
尚硅谷_宋红康_JDBC课件.md



