jdbc练习程序

读取book.txt文件，将文件里图书信息保存进数据库
文件格式
1 鹿鼎记 金庸 500 三联出版社
…
要求：
一、建立表结构
二、针对图书的操作提取出业务接口
实现功能：
1、图书保存
2、可以按照图书编号修改对应的图书信息
3、可以根据id加载图书信息
4、可以根据id删除图书信息
5、分页查询图书信息（参数：开始页，每页显示多少条）

---

jdbc.properties
设置IP地址、databasename、用户名、密码

url = jdbc:mysql://XXX.XXX.XXX.XXX:3306/databasename?usUnicode=true&characterEncoding=utf8
driverClassName = com.mysql.cj.jdbc.Driver
user = xxx
password = xxx

**驱动加载**

```java
package edu.uestc.commons;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBHelp {
	public static String url ;
	public static String user ;
	public static String password ;
	public static String driverClassName ;
	//加载驱动，只加载一次
	static {
		try {
			//类加载器
			InputStream input = DBHelp.class.getClassLoader().getResourceAsStream("jdbc.properties");
			//加载配置文件
			Properties prop = new Properties();
			prop.load(input);
			driverClassName = prop.getProperty("driverClassName");
			url = prop.getProperty("url");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
			Class.forName(driverClassName );
		} catch (ClassNotFoundException | IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
 	 * 获取数据库连接对象
 	 * @return
 	 * @throws SQLException 不能获取数据库连接时，抛出SQLException
 	 */
	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url,user,password);
	}
	public void free(ResultSet rs,Statement stat,Connection conn) {
		
			try {
				if(rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				try {
					if(stat != null)
						stat.close();
				} catch (Exception e) {
					
				}finally {					
						try {
							if(conn != null)
								conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
				}
			}
	}	
}

一、
建表，这里使用Mysql数据库

create table book(
	id int primary key auto_increment,
	name varchar(20) not null,   
	author varchar(12),
	price int not null,
	publisher varchar(20)
);

二、
提取出接口

package homework;

public interface BookService {
	/**
	 * 保存文件里的书籍
	 */
	public void save(int bookid,String bookname,String author,double price,String publisher);
	/**
	 * 保存一本新书
	 */
	public void save();
	/**
	 * 根据图书id删除图书信息
	 */
	public void delete(int bookid);
	/**
	 * 根据图书id查询图书信息
	 */
	public void selecte(int bookid);
	/**
	 * 分页查询
	 */
	public void paging(int start,int num);
	/**
	 * 按照图书id修改对应的图书信息
	 */
	public void ModifyID(int bookid,int newid);
	public void ModifyName(int bookid,String newname);
	public void ModifyAuthor(int bookid,String newauthor);
	public void ModifyPrice(int bookid,int newprice);
	public void ModifyPblisher(int bookid,String newpublisher);
}

1. 图书保存
两种保存方式，一种是读取txt文件保存里面的书籍，另一种是保存一本新书

/**
	 * 保存文件里的书籍
	 */
	@Override
	public void save() {
		Connection conn = null;
		Statement stat = null;
		try {
			conn = helper.getConnection();
			stat = conn.createStatement();
			try(BufferedReader readfile = new BufferedReader(new FileReader(file))){
				String line = null;
 				while((line = readfile.readLine())!=null) {			
 					String[] words = line.split("    ");				
 					String sql = "INSERT INTO book(id,name,author,price,publisher) VALUES(" 
 										+ words[0] + "," + "'" +  words[1]  + "'" + "," 
 										+ "'" + words[2] + "'" + ","  + words[3] + "," 
 										+"'" +  words[4] + "'" + ");";
 					System.out.println(sql);
 					stat.executeUpdate(sql);	 					
				}				
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}				
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			helper.free(null, stat, conn);
		}	
	}

/**
		 * 保存一本新书
		 */
	@Override
		public void save(int bookid,String bookname,String author,double price,String publisher) {
			int rs = 0;
			Connection conn = null;
			Statement stat = null;
			try {
				conn = helper.getConnection();
				stat = conn.createStatement();
	 			String sql = "INSERT INTO book(id,name,author,price,publisher) VALUES(" 
	 								+ bookid + "," + "'" +  bookname  + "'" + "," 
	 								+ "'" + author + "'" + ","  + price + "," +"'" +  publisher + "'" + ");";
	 			System.out.println(sql); 
	 			rs = stat.executeUpdate(sql);	
	 			if(rs>0) {
	 				System.out.println("保存成功");
	 			}
	 			else
	 				System.out.println("保存失败");
			}catch(SQLException e) {
				e.printStackTrace();
			}finally {
				helper.free(null, stat, conn);
			}			
	}

2. 可以按照图书编号修改对应的图书信息
这里只展示修改id，修改其他信息同理。

	/**
		 * 按照图书id修改对应的图书信息
		 */
		//修改id
	@Override
		public void ModifyID(int bookid,int newid) {
			int rs=0;
			Connection conn = null;
			Statement stat = null;
			try {
				conn = helper.getConnection();
				stat = conn.createStatement();
	 			String sql = "UPDATE book SET id=" + newid + " WHERE id=" + bookid +  ";";
	 			System.out.println(sql);
	 			rs = stat.executeUpdate(sql);	 
	 			if(rs>0) {
	 				System.out.println("修改成功");
	 			}
	 			else
	 				System.out.println("修改失败");
			}catch(SQLException e) {
				e.printStackTrace();
			}finally {			
				helper.free(null, stat, conn);
			}
		}

3. 可以根据id加载图书信息

	/**
		 * 根据图书id查询图书信息
		 */
	@Override
		public void selecte(int bookid) {
			ResultSet rs = null;
			Connection conn = null;
			Statement stat = null;
			try {
				conn = helper.getConnection();
				stat = conn.createStatement();
	 			String sql = "SELECT * FROM book WHERE id=" + bookid + ";";
	 			System.out.println(sql);
	 			rs = stat.executeQuery(sql);	 
	 			while(rs.next()) {
	 				int id=rs.getInt("id");
	 				String name = rs.getString("name");
	 				String author = rs.getString("author");
	 				double price = rs.getDouble("price");
	 				String publisher = rs.getString("publisher");
	 				System.out.println("id: " + id + "  name: " + name + "  author: " + author + "  price: " + price + "  publisher: " + publisher);
	 			}
	 			rs.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}finally {			
				helper.free(null, stat, conn);
			}	
		}

4. 可以根据id删除图书信息

		/**
		 * 根据图书id删除图书信息
		 * @param bookid
		 */
	@Override
		public void delete(int bookid) {
			int rs=0;
			Connection conn = null;
			Statement stat = null;
			try {
				conn = helper.getConnection();
				stat = conn.createStatement();
	 			String sql = "DELETE FROM book WHERE id=" + bookid + ";";
	 			System.out.println(sql);
	 			rs = stat.executeUpdate(sql);	
	 			if(rs>0) {
	 				System.out.println("删除成功");
	 			}
	 			else
	 				System.out.println("删除失败");
			}catch(SQLException e) {
				e.printStackTrace();
			}finally {
				helper.free(null, stat, conn);
			}			
		}

5. 分页查询图书信息（参数：开始页，每页显示多少条）

	/**
		 * 分页查询
		 */
	@Override
		public void paging(int start,int num) {
			ResultSet rs = null;
			Connection conn = null;
			Statement stat = null;
			try {
				conn = helper.getConnection();
				stat = conn.createStatement();
	 			String sql = "SELECT * FROM book ORDER BY ID LIMIT " +  (start-1) + "," + num + ";";
	 			System.out.println(sql);
	 			rs = stat.executeQuery(sql);	 
	 			while(rs.next()) {
	 				int id=rs.getInt("id");
	 				String name = rs.getString("name");
	 				String author = rs.getString("author");
	 				double price = rs.getDouble("price");
	 				String publisher = rs.getString("publisher");
	 				System.out.println("id: " + id + "  name: " + name + "  author: " + author + "  price: " + price + "  publisher: " + publisher);
	 			}
	 			rs.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}finally {			
				helper.free(null, stat, conn);
			}	
		}