15.JDBC (java database connection)
Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
- 反射
- JDBC的驱动jar包 mysql-connector-java.5.1.49.jar
15.1 Connection （数据库的连接）
A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.
数据库连接(会话)，声明数据库连接的信息。连接获取sql语句的执行statement和返回执行的结果。
JDBCUtil.java
package com.shangguan.jdbc.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @ClassName: JDBCUtil
* @Description: 数据库连接的工具类
* @Author: 一尘
* @Date: 2020 年 08 月 26 9:27
* @Version 1.0
*/
public class JDBCUtil {
// 数据库连接的驱动
private static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//数据库的url
private static String JDBC_URL = "jdbc:mysql://localhost:3306/hello?useSSL=true";
//用户名
private static String JDBC_USER = "root";
//密码
private static String JDBC_PASSWORD = "root";
//获取数据库连接的方法
public static Connection getConnection(){
Connection connection = null;
//我们要通过驱动去连接数据库
//获取数据库的驱动
try {
//加载驱动
Class.forName(JDBC_DRIVER);
//DriverManager
connection = DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASSWORD);
//jdk1.7异常链处理
} catch (ClassNotFoundException|SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void close(Connection con, Statement statement , ResultSet resultSet){
if(con!=null){
try {
con.close();
con=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
statement=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if(resultSet!=null){
try {
resultSet.close();
resultSet=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Book.java
package com.shangguan.jdbc.beans;
/**
* @ClassName: Book
* @Description: book
* @Author: 一尘
* @Date: 2020 年 08 月 26 9:49
* @Version 1.0
*/
public class Book {
//主键
private Integer bookId;
private String bookName;
private String bookAuthor;
private double bookPrice;
private String bookPublish;
private String bookImg;
public Book() {
}
public Book(String bookName, String bookAuthor, double bookPrice, String bookPublish, String bookImg) {
this.bookName = bookName;
this.bookAuthor = bookAuthor;
this.bookPrice = bookPrice;
this.bookPublish = bookPublish;
this.bookImg = bookImg;
}
public Book(Integer bookId, String bookName, String bookAuthor, double bookPrice, String bookPublish, String bookImg) {
this.bookId = bookId;
this.bookName = bookName;
this.bookAuthor = bookAuthor;
this.bookPrice = bookPrice;
this.bookPublish = bookPublish;
this.bookImg = bookImg;
}
public Integer getBookId() {
return bookId;
}
public void setBookId(Integer bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getBookAuthor() {
return bookAuthor;
}
public void setBookAuthor(String bookAuthor) {
this.bookAuthor = bookAuthor;
}
public double getBookPrice() {
return bookPrice;
}
public void setBookPrice(double bookPrice) {
this.bookPrice = bookPrice;
}
public String getBookPublish() {
return bookPublish;
}
public void setBookPublish(String bookPublish) {
this.bookPublish = bookPublish;
}
public String getBookImg() {
return bookImg;
}
public void setBookImg(String bookImg) {
this.bookImg = bookImg;
}
@Override
public String toString() {
return "Book{" +
"bookId=" + bookId +
", bookName='" + bookName + '\'' +
", bookAuthor='" + bookAuthor + '\'' +
", bookPrice=" + bookPrice +
", bookPublish='" + bookPublish + '\'' +
", bookImg='" + bookImg + '\'' +
'}';
}
}
BookDao.java
package com.shangguan.jdbc.dao;
import com.shangguan.jdbc.beans.Book;
import java.util.List;
public interface BookDao {
//查询所有数据
List<Book> queryAll();
//根据id查询
Book queryById(int bookId);
//模糊查询
List<Book> queryLikeBookName(String bookName);
//添加数据
int addBook(Book book);
//修改数据
int updateBook(Book book);
//删除数据
int deleteBook(Book book);
}
15.2 Statement
The object used for executing a static SQL statement and returning the results it produces.
执行静态的Sql语句对象返回结果.静态sql就是定义死的Sql语句。
- execute()执行sql语句。
15.3 ResultSet
A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
返回数据库的结果集对象。
package com.shangguan.jdbc.impl;
import com.shangguan.jdbc.beans.Book;
import com.shangguan.jdbc.dao.BookDao;
import com.shangguan.jdbc.utils.JDBCUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName: BookDaoImpl
* @Description:
* @Author: 一尘
* @Date: 2020 年 08 月 26 9:56
* @Version 1.0
*/
public class BookDaoImpl implements BookDao {
//获取到数据库的连接
Connection connection = JDBCUtil.getConnection();
Statement statement = null;
ResultSet resultSet = null;
@Override
public List<Book> queryAll() {
List<Book> list = new ArrayList<>();
String sql = "select * from book";
try {
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
//遍历结果集
while(resultSet.next()){
Book book = new Book();
book.setBookId(resultSet.getInt(1));
book.setBookName(resultSet.getString(2));
book.setBookAuthor(resultSet.getString(3));
book.setBookPrice(resultSet.getDouble(4));
book.setBookPublish(resultSet.getString(5));
book.setBookImg(resultSet.getString(6));
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public Book queryById(int bookId) {
Book book = null;
String sql = "select * from book where bookid = 2 ";
try {
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if(resultSet.next()){
book = new Book();
book.setBookId(resultSet.getInt("bookid"));
book.setBookName(resultSet.getString("bookname"));
book.setBookAuthor(resultSet.getString("bookauthor"));
book.setBookPrice(resultSet.getDouble("bookprice"));
book.setBookPublish(resultSet.getString("bookpublish"));
book.setBookImg(resultSet.getString("bookimg"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return book;
}
@Override
public List<Book> queryLikeBookName(String bookName) {
List<Book> list = new ArrayList<>();
String sql = "select * from book where bookname like concat('%','水浒','%')";
try {
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
//遍历结果集
while(resultSet.next()){
Book book = new Book();
book.setBookId(resultSet.getInt(1));
book.setBookName(resultSet.getString(2));
book.setBookAuthor(resultSet.getString(3));
book.setBookPrice(resultSet.getDouble(4));
book.setBookPublish(resultSet.getString(5));
book.setBookImg(resultSet.getString(6));
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public int addBook(Book book) {
int result = 0;
String sql = "insert into book values(null,'围城','钱钟书',99.99,'人民出版社','images/1.png')" ;
try {
statement = connection.createStatement();
result = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public int updateBook(Book book) {
int result = 0;
String sql = "update book set bookname = '骆驼祥子',bookauthor='老舍',bookprice=120.00,bookpublish='工业出版社',bookimg='images/2.png' where bookid = 126 ";
try {
statement = connection.createStatement();
result = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public int deleteBook(Book book) {
int result = 0;
String sql = "delete from book where bookid = 126";
try {
statement = connection.createStatement();
result = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
我们发现一些问题:
- 我们使用的是statement 所以sql语句是静态sql ，数据必须写死 不灵活
- 在resultset中封装数据存在大量重复的代码。在原生的jdbc中必须这样做
- 封装好的JDBC库,DBUtils
15.4 PreparedStatement(预编译的SQl)
-
使用?先进行占位
-
在根据参数的数据类型调用对应的方法设置参数的实际值到参数对应的占位符所在的位置。
-
参数的下标是从1开始。
package com.shangguan.jdbc.impl;
import com.shangguan.jdbc.beans.Book;
import com.shangguan.jdbc.dao.BookDao;
import com.shangguan.jdbc.utils.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName: BookDaoImpl2
* @Description:
* @Author: 一尘
* @Date: 2020 年 08 月 26 10:53
* @Version 1.0
*/
public class BookDaoImpl2 implements BookDao {
Connection connection = JDBCUtil.getConnection();
//预编译的Sql
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
@Override
public List<Book> queryAll() {
List<Book> list = new ArrayList<>();
String sql = "select * from book";
try {
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
Book book = new Book();
book.setBookId(resultSet.getInt("bookid"));
book.setBookName(resultSet.getString("bookname"));
book.setBookAuthor(resultSet.getString("bookauthor"));
book.setBookPrice(resultSet.getDouble("bookprice"));
book.setBookPublish(resultSet.getString("bookpublish"));
book.setBookImg(resultSet.getString("bookimg"));
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public Book queryById(int bookId) {
Book book = null;
//?就是占位符
String sql = "select * from book where bookid = ?";
try {
preparedStatement = connection.prepareStatement(sql);
//将实际的参数设置到坑位上
preparedStatement.setInt(1,bookId);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
book = new Book();
book.setBookId(resultSet.getInt("bookid"));
book.setBookName(resultSet.getString("bookname"));
book.setBookAuthor(resultSet.getString("bookauthor"));
book.setBookPrice(resultSet.getDouble("bookprice"));
book.setBookPublish(resultSet.getString("bookpublish"));
book.setBookImg(resultSet.getString("bookimg"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return book;
}
@Override
public List<Book> queryLikeBookName(String bookName) {
List<Book> list = new ArrayList<>();
String sql = "select * from book where bookname like concat('%',?,'%')";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,bookName);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
Book book = new Book();
book.setBookId(resultSet.getInt("bookid"));
book.setBookName(resultSet.getString("bookname"));
book.setBookAuthor(resultSet.getString("bookauthor"));
book.setBookPrice(resultSet.getDouble("bookprice"));
book.setBookPublish(resultSet.getString("bookpublish"));
book.setBookImg(resultSet.getString("bookimg"));
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public int addBook(Book book) {
int result = 0;
String sql = "insert into book values(null,?,?,?,?,?)";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,book.getBookName());
preparedStatement.setString(2,book.getBookAuthor());
preparedStatement.setDouble(3,book.getBookPrice());
preparedStatement.setString(4,book.getBookPublish());
preparedStatement.setString(5,book.getBookImg());
result = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public int updateBook(Book book) {
int result = 0;
String sql = "update book set bookname = ?,bookauthor=?,bookprice = ?,bookpublish = ?,bookimg = ? where bookid = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,book.getBookName());
preparedStatement.setString(2,book.getBookAuthor());
preparedStatement.setDouble(3,book.getBookPrice());
preparedStatement.setString(4,book.getBookPublish());
preparedStatement.setString(5,book.getBookImg());
preparedStatement.setInt(6,book.getBookId());
result = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public int deleteBook(Book book) {
int result = 0;
String sql = "delete from book where bookid = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,book.getBookId());
result = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
15.5 单元测试(JUNIT)
- junit 最低是4.12
- 需要junit的依赖包
- harmcrest-core
package com.shangguan.jdbc.test;
import com.shangguan.jdbc.beans.Book;
import com.shangguan.jdbc.dao.BookDao;
import com.shangguan.jdbc.impl.BookDaoImpl2;
import org.junit.Test;
import java.util.List;
/**
* @ClassName: BookTest2
* @Description:
* @Author: 一尘
* @Date: 2020 年 08 月 26 10:57
* @Version 1.0
*/
public class BookTest2 {
BookDao bookDao = new BookDaoImpl2();
@Test
public void queryAll(){
List<Book> books = bookDao.queryAll();
for (Book book : books) {
System.out.println(book);
}
}
@Test
public void queryById(){
System.out.println(bookDao.queryById(11));
}
@Test
public void queryLikeName(){
List<Book> list = bookDao.queryLikeBookName("水浒");
for (Book book : list) {
System.out.println(book);
}
}
@Test
public void addBook(){
Book book = new Book("被窝是青春的坟墓","七堇年",99.99,"文艺出版社","images/3.jpg");
System.out.println(bookDao.addBook(book));
}
@Test
public void updateBook(){
Book book = new Book(127,"灯下尘","七堇年",99.99,"文艺出版社","images/3.jpg");
System.out.println(bookDao.updateBook(book));
}
@Test
public void deleteBook(){
Book book = new Book();
book.setBookId(127);
System.out.println(bookDao.deleteBook(book));
}
}
接口实现类完成以后，里面所有的方法必须先经过单元测试，测试方法的完整性，再在后续的程序中进行调用。
427
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
