一、Jdbc的封装与分页
- 封装Jdbc类
public class JdbcUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
Properties pro = new Properties();
try {
pro.load(ClassLoader.getSystemResourceAsStream("jdbc.properties"));
driver = pro.getProperty("driver");
url = pro.getProperty("url");
username = pro.getProperty("username");
password = pro.getProperty("password");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
public static int update(String sql,Object...parms) {
int count = 0;
Connection con = null;
PreparedStatement pstms = null;
try {
con = getConnection();
if (parms != null){
pstms = con.prepareStatement(sql);
for (int i = 0; i < parms.length; i++) {
pstms.setObject(i + 1,parms[i]);
}
count = pstms.executeUpdate();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(null,pstms,con);
}
return count;
}
public static <T> List<T> findAll(Class<T> cls,String sql,Object...params){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet res = null;
List<T> list = new ArrayList<T>();
try {
con = getConnection();
pstmt = con.prepareStatement(sql);
if (params != null){
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i+1, params[i]);
}
}
res = pstmt.executeQuery();
//获取到数据库返回的元数据
ResultSetMetaData metaData = res.getMetaData();
//获取返回数据中的列数
int columnCount = metaData.getColumnCount();
while (res.next()){
//通过对象的无参构造，来反射创建一个对象
T t = cls.newInstance();
//循环列数，为对象设置参数
for (int i = 1; i <= columnCount; i++) {
//获取该列的列名
String cName = metaData.getColumnLabel(i);
//通过反射获取定义的属性，并暴力反射为该属性设置值
Field field = cls.getDeclaredField(cName);
field.setAccessible(true);
field.set(t,res.getObject(cName));
}
list.add(t);
}
} catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException throwables) {
throwables.printStackTrace();
}
return list;
}
public static <T> T findByPro(Class<T> cls,String sql,Object...params){
Connection con = null;
PreparedStatement pstmst = null;
ResultSet res = null;
T t = null;
try {
con = getConnection();
pstmst = con.prepareStatement(sql);
if (params != null){
for (int i = 0; i < params.length; i++) {
pstmst.setObject(i + 1,params[i]);
}
}
res = pstmst.executeQuery();
ResultSetMetaData metaData = res.getMetaData();
int columnCount = metaData.getColumnCount();
while (res.next()){
t = cls.newInstance();
for (int i = 1; i <= columnCount; i++) {
String cNmae = metaData.getColumnLabel(i);
Field field = cls.getDeclaredField(cNmae);
field.setAccessible(true);
field.set(t,res.getObject(cNmae));
}
}
} catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException throwables) {
throwables.printStackTrace();
}
return t;
}
public static void close(ResultSet res, Statement stmt, Connection con){
if (res != null){
try {
res.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con != null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
- 定义Book类
public class Book {
private int id;
private String bookName;
private int bookCount;
private String detail;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public int getBookCount() {
return bookCount;
}
public void setBookCount(int bookCount) {
this.bookCount = bookCount;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", bookName='" + bookName + '\'' +
", bookCount=" + bookCount +
", detail='" + detail + '\'' +
'}';
}
}
- 定义分页类Page
public class Page<T> {
//当期页码
private int pageIndex;
//一页显示的数据数量
private int pageSize;
//总记录数
private int total;
//总页数
private int pageCount;
//查询的数据，使用泛型
private List<T> data = new ArrayList<>();
public int getPageIndex() {
return pageIndex;
}
public void setPageIndex(int pageIndex) {
this.pageIndex = pageIndex;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public int getPageCount() {
//取余计算总的页数，判断是否整除
if (total % pageSize == 0){
pageCount = total / pageSize;
}else {
pageCount = total / pageSize + 1;
}
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
}
- 定义Book 类的Dao接口
public interface BookDao {
public List<Book> findAll();
public Book findById(int id);
public int addBook(Book book);
public int delBook(int id);
public int updateBook(Book book);
public int updateName(int id,String name);
Page<Book> getAll(int startIndex, int pageSize);
int count();
}
- 定义Dao接口的实现类
public class BookDaoImpl implements BookDao{
@Override
public List<Book> findAll() {
String sql = "select * from books";
return JdbcUtils.findAll(Book.class,sql,null);
}
@Override
public Book findById(int id) {
String sql = "select * from books where id =?";
return JdbcUtils.findByPro(Book.class,sql,id);
}
@Override
public int addBook(Book book) {
String sql = "insert into books values(null,?,?,?)";
return JdbcUtils.update(sql,book.getBookName(),book.getBookCount(),book.getDetail());
}
@Override
public int delBook(int id) {
String sql ="delete from books where id=?";
return JdbcUtils.update(sql,id);
}
@Override
public int updateBook(Book book) {
String sql = "update books set bookName=?,bookCount=?,detail=? where id=?";
return JdbcUtils.update(sql,book.getBookName(),book.getBookCount(),book.getBookCount(),book.getId());
}
@Override
public int updateName(int id, String name) {
String sql = "update books set bookName=? where id=?";
return JdbcUtils.update(sql,name,id);
}
@Override
public Page<Book> getAll(int pageIndex, int pageSize) {
String sql = "select * from books limit ?,?";
int total = count();
Page<Book> page = new Page<Book>();
page.setPageIndex(pageIndex);
page.setPageSize(pageSize);
page.setTotal(total);
if (total > 0){
List<Book> list = JdbcUtils.findAll(Book.class, sql, (page.getPageIndex() - 1) * pageSize, pageSize);
page.setData(list);
}
return page;
}
@Override
public int count() {
String sql = "select count(*) from books";
Connection con = null;
PreparedStatement pstmts = null;
ResultSet res = null;
try {
con = JdbcUtils.getConnection();
pstmts = con.prepareStatement(sql);
res = pstmts.executeQuery();
if (res.next()){
return res.getInt(1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.close(res,pstmts,con);
}
return 0;
}
}
- 测试运行
public class Test {
public static void main(String[] args) {
BookDao bookDao = new BookDaoImpl();
List<Book> all = bookDao.findAll();
all.forEach(System.out::println);
Book byId = bookDao.findById(1);
System.out.println(byId);
Book book = new Book();
book.setId(13);
book.setBookName("积分第三节");
book.setBookCount(12);
book.setDetail("789");
bookDao.updateBook(book);
bookDao.delBook(11);
bookDao.updateName(13,"9898989");
bookDao.findAll().forEach(System.out::println);
System.out.println(bookDao.count());
System.out.println("============");
List<Book> data = bookDao.getAll(2, 5).getData();
data.forEach(System.out::println);
}
private static void test() {
List<Book> all = JdbcUtils.findAll(Book.class, "select * from books", null);
all.forEach(System.out::println);
System.out.println("--------------");
Book byPro = JdbcUtils.findByPro(Book.class, "select * from books where id=?", 1);
System.out.println(byPro);
}
}
- 所需配置文件
- jdbc.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///ssm?serverTimezone=UTC&characterEncoding=utf8
username=root
password=123456
- 数据库文件
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bookName` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`bookCount` int(11) NULL DEFAULT NULL,
`detail` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES (1, 'java从入门到精通', 30, '菜鸟阶层');
INSERT INTO `books` VALUES (2, 'springboot', 30, '菜鸟阶层');
INSERT INTO `books` VALUES (3, 'mysql', 10, '菜鸟阶层');
INSERT INTO `books` VALUES (4, 'jquery', 60, '菜鸟阶层');
INSERT INTO `books` VALUES (5, 'C#', 25, '菜鸟阶层');
INSERT INTO `books` VALUES (6, 'vue.js', 24, '菜鸟阶层');
INSERT INTO `books` VALUES (7, 'react', 66, '大师阶级');
INSERT INTO `books` VALUES (8, 'Ajax', 24, '菜鸟阶级');
INSERT INTO `books` VALUES (9, 'spring4', 22, '入门阶级');
INSERT INTO `books` VALUES (10, 'spring5', 22, '入门阶级');
SET FOREIGN_KEY_CHECKS = 1;
二、数据库连接池
2.1 C3P0的配置文件与基本使用
-
导包
- 导入C3P0的包c3p0-0.9.5.2.jar，mchange-commons-java-0.2.12.jar
- 导入mysql驱动包
-
定义配置文件
- 名称： c3p0.properties 或者 c3p0-config.xml
- 路径：直接将文件放在src目录下即可。
-
创建核心对象 数据库连接池对象 ComboPooledDataSource
-
获取连接： getConnection
c3p0-config.xml配置文件：
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- c3p0默认配置，下面还可以配置多个数据库 -->
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 初始化时获取连接数 -->
<property name="initialPoolSize">6</property>
<!-- 连接池中保留的最大连接数 -->
<property name="maxPoolSize">50</property>
<!-- 最大空闲时间，多少秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0 -->
<property name="maxIdleTime">1000</property>
</default-config>
</c3p0-config>
测试代码：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) throws SQLException {
ComboPooledDataSource pooledDataSource = new ComboPooledDataSource();
Connection con = pooledDataSource.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from books");
ResultSet res = pstmt.executeQuery();
while (res.next()){
System.out.println(res.getString("bookName"));
}
}
}
2.12 Druid的基本使用
- 导入jar包 druid-1.0.9.jar
- 定义配置文件：
- 是properties形式的
- 可以叫任意名称，可以放在任意目录下
- 加载配置文件。Properties
- 获取数据库连接池对象：通过工厂来来获取 DruidDataSourceFactory
- 获取连接：getConnection
Druid配置文件（druid.properties）：
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///ssm?serverTimezone=UTC&characterEncoding=utf8
username=root
password=root
#初始化时建立物理连接的个数
initialSize=6
#最大连接池数量
maxActive=8
#最小连接池数量
minIdle=3
#获取连接时最大等待时间，单位毫秒
maxWait=12000
测试类：
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public class TestDruid {
public static void main(String[] args) throws Exception {
Properties pro = new Properties();
InputStream input = TestDruid.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(input);
DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
Connection con = dataSource.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from books");
ResultSet res = pstmt.executeQuery();
while (res.next()){
System.out.println(res.getString("bookName"));
}
}
}
三、JdbcTemplate的基本使用
-
导入jar包，红色为JdbcTemplate所需要的spring包，蓝色为Test测试所需的包
-
创建JdbcTemplate对象。依赖于数据源DataSource
- JdbcTemplate template = new JdbcTemplate(ds);
-
调用JdbcTemplate的方法来完成CRUD的操作
- update()：执行DML语句，增、删、改语句
- queryForMap()：查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
- 注意：这个方法查询的结果集长度只能是1
- queryForList()：查询结果将结果集封装为list集合
- 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
- query()：查询结果，将结果封装为JavaBean对象
- query的参数：RowMapper
- 一般我们使用BeanPropertyRowMapper实现类，可以完成数据到JavaBean的自动封装
- new BeanPropertyRowMapper<类型>(类型.class)
- queryForObject：查询结果，将结果封装为对象
- 一般用于聚合函数的查询
案例：
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.Properties;
public class TestTemplate {
private static DataSource dataSource = null;
private JdbcTemplate template = new JdbcTemplate(dataSource);
static {
Properties pro = new Properties();
InputStream input = TestDruid.class.getClassLoader().getResourceAsStream("druid.properties");
try {
pro.load(input);
} catch (IOException e) {
e.printStackTrace();
}
try {
dataSource = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void testUpdate(){
String sql = "update books set bookCount=100 where id=1";
int count = template.update(sql);
System.out.println(count);
}
@Test
public void testAdd(){
String sql = "insert into books values(?,?,?,?)";
int count = template.update(sql, null, "格林童话", 25, "入门阶级");
System.out.println(count);
}
@Test
public void testDel(){
String sql = "delete from books where id=?";
int count = template.update(sql, 13);
System.out.println(count);
}
@Test
public void testFindById(){
String sql = "select * from books where id=?";
Book book = template.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), 1);
System.out.println(book);
}
@Test
public void testFindAll(){
String sql = "select * from books";
List<Map<String, Object>> maps = template.queryForList(sql);
maps.forEach(System.out::println);
}
@Test
public void testFindAll2(){
String sql = "select * from books";
List<Book> list = template.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
list.forEach(System.out::println);
}
@Test
public void testCount(){
String sql = "select count(*) from books";
Long total = template.queryForObject(sql, Long.class);
System.out.println(total);
}
}
75
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
