package dao;
import java.sql.*;
public class BaseDao {
//在连接数据库之前，准备4个数据：
//加载驱动类：
private static final String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
//连接数据库地址：
private static final String URL="jdbc:sqlserver://localhost:1433;databaseName=hs";
//sql身份登录时的用户名：
private static final String USBR="hs";
//sql身份登录时的密码：
private static final String PWD="123";
//声明连接对象
public static Connection connection;
//声明执行sql的对象
public static PreparedStatement ps;
//接收返回值对象:接收结果是两个或两个以上的时候，用ResultSet接收；
// 结果是一个的时候，使用变量接。
public static ResultSet rs;
//获得数据库连接方法
public static Connection getConnection(){
try {
Class.forName(DRIVER);//1.加载驱动
connection=DriverManager.getConnection(URL,USBR,PWD);//2.获得连接对象
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
//关闭数据库连接;注意关闭顺序，先关rs，再关ps,最后关闭con
public static void closeAll(Connection con,PreparedStatement ps,ResultSet rs){
try {//8.关闭数据库连接对象
if(null!=rs){
rs.close();
}if(null!=ps){
ps.close();
}if(null!=connection){
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

package entity;
public class BookManage {
private int b_id;
private String b_name;
private String b_author;
private String b_time;
private int b_type;
public int getB_id() {
return b_id;
}
public void setB_id(int bId) {
b_id = bId;
}
public String getB_name() {
return b_name;
}
public void setB_name(String bName) {
b_name = bName;
}
public String getB_author() {
return b_author;
}
public void setB_author(String bAuthor) {
b_author = bAuthor;
}
public String getB_time() {
return b_time;
}
public void setB_time(String bTime) {
b_time = bTime;
}
public int getB_type() {
return b_type;
}
public void setB_type(int bType) {
b_type = bType;
}
}

package dao;
import java.util.ArrayList;
import entity.BookManage;
public interface BookManageDao {
public int insert(BookManage book);//参数为了给SQL语句赋值
public int delete(BookManage book);
public int update(BookManage book);
public BookManage selectByid(BookManage book);
public ArrayList<BookManage> selectAll();
}

package dao.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import dao.BaseDao;
import dao.BookManageDao;
import entity.BookManage;
public class BookManageDaoImpl extends BaseDao implements BookManageDao {
//增：
public int insert(BookManage book) {
String sql="insert into dbo.BookManage values(?,?,?,?)";
int i=0;
try {
ps=getConnection().prepareStatement(sql);
ps.setString(1,book.getB_name());
ps.setString(2,book.getB_author());
ps.setString(3,book.getB_time());
ps.setInt(4,book.getB_type());
i=ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
this.closeAll(connection, ps, rs);
}
return i;
}
//删：
public int delete(BookManage book) {
String sql="delete from BookManage where b_id=?";
int i=0;
try {
ps = getConnection().prepareStatement(sql);
ps.setInt(1, book.getB_id());
i=ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
this.closeAll(connection, ps, rs);
}
return i;
}
//改：
public int update(BookManage book) {
String sql="update BookManage set b_name=?,b_author=?,b_time=?,b_type=? where b_id=?";
int i=0;
try {
ps=getConnection().prepareStatement(sql);
ps.setString(1, book.getB_name());
ps.setString(2, book.getB_author());
ps.setString(3, book.getB_time());
ps.setInt(4, book.getB_type());
ps.setInt(5, book.getB_id());
i=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
this.closeAll(connection, ps, rs);
}
return i;
}
//查所有：
public ArrayList<BookManage> selectAll() {
ArrayList<BookManage> list=new ArrayList<BookManage>();
try {
String sql="select * from BookManage";
ps=getConnection().prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
BookManage book=new BookManage();
book.setB_id(rs.getInt(1));
book.setB_name(rs.getString(2));
book.setB_author(rs.getString(3));
book.setB_time(rs.getString(4));
book.setB_type(rs.getInt(5));
list.add(book);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
this.closeAll(connection, ps, rs);
}
return list;
}
//根据ID查询：
public BookManage selectByid(BookManage book) {
String sql="select * from dbo.BookManage where b_id=?";
try {
ps=getConnection().prepareStatement(sql);
ps.setInt(1,book.getB_id());
rs=ps.executeQuery();
if(rs.next()){
book.setB_id(rs.getInt(1));
book.setB_name(rs.getString(2));
book.setB_author(rs.getString(3));
book.setB_time(rs.getString(4));
book.setB_type(rs.getInt(5));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
this.closeAll(connection, ps, rs);
}
return book;
}
}

package text;
import java.util.ArrayList;
import java.util.Scanner;
import dao.impl.BookManageDaoImpl;
import entity.BookManage;
public class text {
static BookManageDaoImpl bookdaoimpl=new BookManageDaoImpl();
static BookManage book=new BookManage();
static Scanner input=new Scanner(System.in);
public static void main(String[] args) {
new text().sel_all();
}
//添加：
public void ins() {
//Scanner input=new Scanner(System.in);
//System.out.println("请输入ID：");
//book.setB_id(input.nextInt());
System.out.println("请输入书名：");
book.setB_name(input.next());
System.out.println("请输入作者：");
book.setB_author(input.next());
System.out.println("请输入购买时间：");
book.setB_time(input.next());
System.out.println("请输入类型：");
book.setB_type(input.nextInt());
int i=bookdaoimpl.insert(book);
if(i>0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
}
//删除
public void del() {
//book.setB_id(1);
System.out.println("请输入要删除的书籍编号：");
book.setB_id(input.nextInt());
int i=bookdaoimpl.delete(book);
if(i>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
//修改：
public void upd(){
System.out.println("请输入要修改的书籍编号：");
book.setB_id(input.nextInt());
System.out.println("请输入书名：");
book.setB_name(input.next());
System.out.println("请输入作者：");
book.setB_author(input.next());
System.out.println("请输入购买时间：");
book.setB_time(input.next());
System.out.println("请输入类型：");
book.setB_type(input.nextInt());
int i=bookdaoimpl.update(book);
if(i>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
}
//查询：
public void sel_all(){
ArrayList<BookManage> s=bookdaoimpl.selectAll();
System.out.println("全部书籍信息：");
System.out.println("编号\t书名\t作者\t购买时间\t\t\t类型\t");
for(int i=0;i<s.size();i++){
System.out.println(s.get(i).getB_id()+"\t"+s.get(i).getB_name()+"\t"+s.get(i).getB_author()+"\t"+s.get(i).getB_time()+"\t"+s.get(i).getB_type()+"\t");
}
}
//根据ID查询
public void sel_id(){
System.out.println("请输入要查询的书籍编号：");
book.setB_id(input.nextInt());
BookManage s=bookdaoimpl.selectByid(book);
System.out.println("书籍编号是"+s.getB_id()+"的书籍信息是：");
System.out.println("编号\t书名\t作者\t购买时间\t\t\t类型\t");
System.out.println(s.getB_id()+"\t"+s.getB_name()+"\t"+s.getB_author()+"\t"+s.getB_time()+"\t"+s.getB_type()+"\t");
}
}

1CAB98AEC94D82A2A28BEFD3E8672429

好厉害=w=