JDBC基本操作的Demo程序

数据库建表文件

createTableBook.sql

CREATE database IF NOT EXISTS testing;
USE testing;
DROP TABLE IF EXISTS book;
CREATE TABLE book(
	id int PRIMARY KEY,
	bookName varchar(20),
	price int,
	description varchar(100)
);

JDBC的配置文件

config/jdbc.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/testing?useUnicode=true&characterEncoding=UTF-8
username=root
password=123456

程序代码

com.hitmt.entity.Book.java

package com.hitmt.entity;
/**
 * <p>Description: book实体类，对应了数据库的一张book表</p>
 * @author HiTMT
 * @date 2019年10月10日上午12:13:27
 */
public class Book {
	private int id;
	private String bookName;
	private int price;
	private String description;
	
	public Book(int id, String bookName, int price, String description) {
		super();
		this.id = id;
		this.bookName = bookName;
		this.price = price;
		this.description = description;
	}
	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 getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	@Override
	public String toString() {
		return "Book [id=" + id + ", bookName=" + bookName + ", price=" + price + ", description=" + description + "]";
	}
}

com.hitmt.dao.BookDao.java

package com.hitmt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hitmt.entity.Book;
import com.hitmt.jdbc.DBUtil;

/**
 * <p>Description: 操作数据库book表的数据访问对象</p>
 * @author HiTMT
 * @date 2019年10月10日上午12:17:28  
 */
public class BookDao {
	public static void insertBook(Book book) {
		String sql="insert into Book(id,bookName,price,description) values(?,?,?,?);";
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=DBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, book.getId());
			ps.setString(2, book.getBookName());
			ps.setInt(3, book.getPrice());
			ps.setString(4, book.getDescription());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(ps!=null)  DBUtil.close(ps);
			if(conn!=null)  DBUtil.close(conn);
		}
	}
	public static void deleteBook(int id) {
		String sql="delete from Book where id=?;";
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=DBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(ps!=null)  DBUtil.close(ps);
			if(conn!=null)  DBUtil.close(conn);
		}
	}
	public static void deleteBook(Book book) {
		deleteBook(book.getId());
	}
	public static Book selectBook(int id) {
		String sql="select * from book where id=?;";
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Book book=null;
		try {
			conn=DBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				String bookName=rs.getString("bookName");
				int price=rs.getInt("price");
				String description=rs.getString("description");
				book=new Book(id, bookName, price, description);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs!=null)  DBUtil.close(rs);
			if(ps!=null)  DBUtil.close(ps);
			if(conn!=null)  DBUtil.close(conn);
		}
		return book;
	}
	public static List<Book> selectBooks(String bookName) {
		String sql="select * from book where bookName=?;";
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Book> books=new ArrayList<>();
		try {
			conn=DBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setString(1, bookName);
			rs=ps.executeQuery();
			while(rs.next()) {
				int id=rs.getInt("id");
				int price=rs.getInt("price");
				String description=rs.getString("description");
				books.add(new Book(id, bookName, price, description));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs!=null)  DBUtil.close(rs);
			if(ps!=null)  DBUtil.close(ps);
			if(conn!=null)  DBUtil.close(conn);
		}
		return books;
	}
	/**
	 * 按newBook的id匹配数据的旧书本，然后更新其它字段
	 * @param newBook
	 */
	public static void updateBook(Book newBook) {
		String sql="update book set bookName=?,price=?,description=? where id=?;";
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=DBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setString(1, newBook.getBookName());
			ps.setInt(2, newBook.getPrice());
			ps.setString(3, newBook.getDescription());
			ps.setInt(4, newBook.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(ps!=null)  DBUtil.close(ps);
			if(conn!=null)  DBUtil.close(conn);
		}
	}
}

com.hitmt.jdbc.DBUtil.java

package com.hitmt.jdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
 * <p>Description: 管理数据库的工具</p>
 * @author HiTMT
 * @date 2019年10月10日上午12:12:41
 */
public class DBUtil {
	private static String driverClassName;
	private static String url;
	private static String username;
	private static String password;
	
	static {
		FileInputStream fin;
		Properties prp=new Properties();
		try {
			fin=new FileInputStream("config/jdbc.properties");
			prp.load(fin);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		driverClassName=prp.getProperty("driverClassName");
		url=prp.getProperty("url");
		username=prp.getProperty("username");
		password=prp.getProperty("password");
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() {
		Connection conn=null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	public static void close(Connection conn) {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void close(Statement sta) {
		try {
			sta.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void close(ResultSet rs) {
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

com.hitmt.test.Tester.java

package com.hitmt.test;

import com.hitmt.dao.BookDao;
import com.hitmt.entity.Book;

/**
 * <p>Description: 测试类，测试DBUtil的基本功能和数据库Book表的增删查改</p>
 * @author HiTMT
 * @date 2019年10月10日上午12:15:40  
 */
public class Tester {

	public static void main(String[] args) {
		Book b1=new Book(1, "软件工程导论", 40, "软件工程专业的教材书，引导学生了解该专业");
		BookDao.insertBook(b1);
		System.out.println("插入完成，插入的书本信息为："+b1);
		Book b1t=BookDao.selectBook(1);
		System.out.println("查询完成，查询到 id=1 书本信息为："+b1t);
		BookDao.updateBook(new Book(1, "了解软件工程", 30, "引导学生了解软件工程专业"));
		System.out.println("修改完成");
		b1t=BookDao.selectBook(1);
		System.out.println("查询完成，查询更新后的书本信息为："+b1t);
		BookDao.deleteBook(b1.getId());
		System.out.println("删除完成，删除的书本 id="+b1.getId());
		b1t=BookDao.selectBook(1);
		if(b1t==null) {
			System.out.println("删除 id 为 1 的书本成功");
		}else {
			System.out.println("删除 id 为 1 的书本失败");
		}
	}

}

执行程序的效果