正文
JDBC简单增删改查实现(单表)
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
0.准备工作
开发工具: MySQL数据库, intelliJ IDEA2017.
准备jar包: mysql-connector-java-5.1.28-bin.jar(其他均可)
1. 数据库数据准备
2. 项目结构图示
3.代码
实体类Book.java
package com.zzuli.entity; import java.util.Date; /**
*
* 图书实体类
* Created by hejjon on 2019/5/25.
*/
public class Book {
private int id;
private String bookName;
private String author;
private Date pubDate; // 出版日期 util.Date public Book() {
} public Book(int id, String bookName, String author, Date pubDate) {
this.id = id;
this.bookName = bookName;
this.author = author;
this.pubDate = pubDate;
} public Book(String bookName, String author, Date pubDate) {
this.bookName = bookName;
this.author = author;
this.pubDate = pubDate;
} public int getId() {
return id;
} public String getBookName() {
return bookName;
} public String getAuthor() {
return author;
} public Date getPubDate() {
return pubDate;
} public void setId(int id) {
this.id = id;
} public void setBookName(String bookName) {
this.bookName = bookName;
} public void setAuthor(String author) {
this.author = author;
} public void setPubDate(Date pubDate) {
this.pubDate = pubDate;
} @Override
public String toString() {
return "Book{" +
"id=" + id +
", bookName='" + bookName + '\'' +
", author='" + author + '\'' +
", pubDate=" + pubDate +
'}';
}
}
Dao层接口 BookDao.java
package com.zzuli.dao; import com.zzuli.entity.Book; import java.util.List; /**
* Created by hejjon on 2019/5/25.
*/
public interface BookDao { /**
* 增添图书
* @param book 要添加的book对象
* @return
*/
int insertBook(Book book); /**
* 删除指定id的图书
* @param id 要删除的图书的id
* @return
*/
int deleteBook(int id); /**
* 修改图书
* @param book 修改后的图书
* @return
*/
int updateBook(Book book); /**
* 查找指定书名的图书
* @param bookName
* @return
*/
Book selectBookByName(String bookName); List<Book> selectAllBook();
}
Dao层实现类: BookDaoImpl.java
package com.zzuli.dao.impl; import com.zzuli.dao.BookDao;
import com.zzuli.entity.Book; import java.sql.*;
import java.util.ArrayList;
import java.util.List; /**
* Created by hejjon on 2019/5/25.
*/
public class BookDaoImpl implements BookDao { private final String jdbcDriver = "com.mysql.jdbc.Driver";
private final String url = "jdbc:mysql://localhost:3306/db_book";
private final String userName = "root";
private final String password = "123123"; @Override
public int insertBook(Book book) {
int n = 0;
String sql = "insert into t_book values (default,?,?,?)";
Connection conn = null;
PreparedStatement ps = null;
java.sql.Date pubDate = null;
try {
// 加载驱动
Class.forName(jdbcDriver);
// 获取连接
conn = DriverManager.getConnection(url, userName, password);
// 创建会话
ps = conn.prepareStatement(sql);
// 设置 ? 的值
ps.setString(1, book.getBookName());
ps.setString(2, book.getAuthor());
pubDate = new java.sql.Date(book.getPubDate().getTime());
ps.setDate(3, pubDate);
// 执行sql
n = ps.executeUpdate(); } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally { // 关闭数据库资源
try {
if (null != ps) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return n;
} @Override
public int deleteBook(int id) {
String sql = "delete from t_book where id=?";
int n = 0;
Connection conn = null;
PreparedStatement ps = null; try {
// 加载驱动
Class.forName(jdbcDriver);
// 获取连接
conn = DriverManager.getConnection(url, userName, password);
// 创建会话
ps = conn.prepareStatement(sql);
// 设置 ? 的值
ps.setInt(1, id);
// 执行sql
n = ps.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != ps) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
} return n;
} @Override
public int updateBook(Book book) {
String sql = "update t_book set bookName=?, author=?, pubDate=? where id=?";
int n = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
// 加载驱动
Class.forName(jdbcDriver);
// 获取连接
conn = DriverManager.getConnection(url, userName, password);
// 创建会话
ps = conn.prepareStatement(sql);
ps.setString(1, book.getBookName());
ps.setString(2, book.getAuthor());
ps.setDate(3, new java.sql.Date(book.getPubDate().getTime()));
ps.setInt(4, book.getId());
n = ps.executeUpdate(); } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != ps) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return n;
} @Override
public Book selectBookByName(String bookName) {
String sql = "select * from t_book where bookName=?";
Book book = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 加载驱动
Class.forName(jdbcDriver);
// 获取连接
conn = DriverManager.getConnection(url, userName, password);
// 创建会话
ps = conn.prepareStatement(sql);
ps.setString(1, bookName);
rs = ps.executeQuery(); if (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("bookName");
String author = rs.getString("author");
java.util.Date pubDate = rs.getDate("pubDate");
book = new Book(id, name, author, pubDate);
} } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != ps) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return book;
} @Override
public List<Book> selectAllBook() {
String sql = "select * from t_book";
List<Book> list = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null; try {
// 加载驱动
Class.forName(jdbcDriver);
// 获取连接
conn = DriverManager.getConnection(url, userName, password);
// 创建会话
ps = conn.prepareStatement(sql);
// 获取结果集
rs = ps.executeQuery();
// 遍历结果集
while (rs.next()) {
int id = rs.getInt("id");
String bookName = rs.getString("bookName");
String author = rs.getString("author");
Date pubDate = rs.getDate("pubDate"); Book book = new Book(id, bookName, author, pubDate);
list.add(book);
} } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != ps) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
} return list;
}
}
测试类:Test.java
package com.zzuli.test; import com.zzuli.dao.BookDao;
import com.zzuli.dao.impl.BookDaoImpl;
import com.zzuli.entity.Book; import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List; /**
* 测试类
* Created by hejjon on 2019/5/25.
*/
public class Test {
public static void main(String[] args) {
// testInsert();
// testDelete();
// sestUpdate();
// testSelect(); testSelectAllBook(); } private static void testSelectAllBook() {
BookDao bookDao = new BookDaoImpl(); List<Book> list = new ArrayList<>(); list = bookDao.selectAllBook(); for (Book book : list) {
System.out.println(book);
}
} // 测试按书名查找图书
public static void testSelect() {
BookDao bookDao = new BookDaoImpl();
Book book = bookDao.selectBookByName("西游记"); System.out.println(book);
} // 测试修改图书方法
public static void testUpdate() {
// 创建dao层实现类对象
BookDao bookDao = new BookDaoImpl();
String dateStr = "1788-06-15";
Date pubDate = null;
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
pubDate = df.parse(dateStr);
} catch (ParseException e) {
e.printStackTrace();
} Book book = new Book(2, "西游记", "吴承恩", pubDate); int i = bookDao.updateBook(book); if (i > 0) {
System.out.println("图书修改成功");
} else {
System.out.println("图书修改失败");
}
} // 测试删除图书方法
public static void testDelete() {
BookDao bookDao = new BookDaoImpl();
int i = bookDao.deleteBook(5); if (i > 0) {
System.out.println("删除图书成功");
} else {
System.out.println("删除图书失败");
}
} // 测试添加图书方法
public static void testInsert() {
// 创建dao层实现类对象
BookDao bookDao = new BookDaoImpl();
String dateStr = "1678-09-24";
Date pubDate = null;
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
pubDate = df.parse(dateStr);
} catch (ParseException e) {
e.printStackTrace();
} Book book = new Book("鹿鼎记", "张三", pubDate); int i = bookDao.insertBook(book); if (i > 0) {
System.out.println("图书添加成功");
} else {
System.out.println("图书添加失败");
}
}
}
4.总结:
java.sql.Date, java.util.Date, String 之间的相互转换:
// String ---> java.util.Date parse()方法
String DateStr = "1997-02-24";
Date date = null;
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
date = df.parse(DateStr);
} catch (ParseException e) {
e.printStackTrace();
} // java.util.Date ---> String format()方法
System.out.println(df.format(date)); // 1997-02-24 System.out.println(System.currentTimeMillis()); // 1558864421743 // java.util.Date ---> java.sql.Date
java.util.Date utilDate = new java.util.Date(1558864204513L); // 是long类型 L别丢了 java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
System.out.println(utilDate); // Sun May 26 17:50:04 CST 2019
System.out.println(sqlDate); // 2019-05-26
