一、准备工作。
1.创建两张表,一张图书信息表,一张用户账号密码表。
相关元素自己定义即可,主键自增也是选设
2.大致布局如下
dbconfig工具类
driverClassName = com.mysql.jdbc.Driver url = jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8 username = root password = 123456
图书信息User类,在这我习惯性取名为User类,在编写的时候可以改为Book类
package com.hqyj.cl.jdbc.book; public class User { private String name; private String author; private Double price; private String country; public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAuthor() { return author; } public void setAuthor(String author) { this.author = author; } public Double getPrice() { return price; } public void setPrice(Double price) { this.price = price; } public String getCountry() { return country; } public void setCountry(String country) { this.country = country; } @Override public String toString() { return " \n书名:" + name + " 作者:"+author+" 价格:"+price+" 国家:"+country+"\t"; } }
功能方法接口类
package com.hqyj.cl.jdbc.book; import java.sql.SQLException; import java.util.List; public interface UserDaoBook { int addUser(String bookName,String author,Double price,String country); int revisePrice(String name,Double price); int delete(String name); List<User> dimQuery(String name); List<User> queryAll(); Reader login(String user,String password); }
功能实现类 依次为外加登入系统
package com.hqyj.cl.jdbc.book; import com.hqyj.cl.jdbc.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class UserDaoBookImpl implements UserDaoBook { Connection con = null;//连接对象 PreparedStatement pre = null;//语句对象 ResultSet re = null;//结果集对象 @Override public int addUser(String name, String author, Double price, String country) { try { con = JDBCUtils.getConnection(); String sql = "INSERT INTO `book` VALUES(null,?,?,?,?);"; pre = con.prepareStatement(sql); pre.setString(1,name); pre.setString(2,author); pre.setDouble(3,price); pre.setString(4,country); return pre.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.closeAll(re,pre,con); System.out.println("添加成功\n"); } return 0; } @Override public int revisePrice(String name,Double price) { try { con = JDBCUtils.getConnection(); String sql = "UPDATE `book` set `price` = ? WHERE `name` =?"; pre = con.prepareStatement(sql); pre.setDouble(1,price); pre.setString(2,name); return pre.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.closeAll(re,pre,con); System.out.println("书名:"+name+" 价格已修改为"+price+"\n"); } return 0; } @Override public int delete(String name) { try { con = JDBCUtils.getConnection(); String sql = "delete from book where name = ?;"; pre = con.prepareStatement(sql); pre.setString(1,name); return pre.executeUpdate(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.closeAll(re,pre,con); System.out.println("书名:"+name+" 已删除\n"); } return 0; } public List<User> dimQuery(String name1){ List<User> list = new ArrayList<>(); try { con = JDBCUtils.getConnection(); String sql = "SELECT * FROM `book` WHERE `name` LIKE concat('%',?,'%')"; pre = con.prepareStatement(sql); pre.setString(1,name1); re = pre.executeQuery(); while(re.next()){ User user = new User(); String name = re.getString("name"); user.setName(name); String author = re.getString("author"); user.setAuthor(author); Double price = re.getDouble("price"); user.setPrice(price); String country = re.getString("country"); user.setCountry(country); list.add(user); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.closeAll(re,pre,con); } return list; } public List<User> queryAll(){ List<User> list = new ArrayList<>(); try { con = JDBCUtils.getConnection(); String sql = "SELECT * FROM `book`"; pre = con.prepareStatement(sql); re = pre.executeQuery(); while(re.next()){ User user = new User(); String name = re.getString("name"); user.setName(name); String author = re.getString("author"); user.setAuthor(author); Double price = re.getDouble("price"); user.setPrice(price); String country = re.getString("country"); user.setCountry(country); list.add(user); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JDBCUtils.closeAll(re,pre,con); } return list; } @Override public Reader login(String user1, String password1) { Reader r = new Reader(); try { con = JDBCUtils.getConnection(); String sql = "SELECT * FROM `user`WHERE `user` = ? AND `password` = ?"; pre = con.prepareStatement(sql); pre.setString(1,user1); pre.setString(2,password1); re = pre.executeQuery(); if (re.next()){ re.previous(); while (re.next()){ String user = re.getString("user"); r.setUser(user); String password = re.getString("password"); r.setPassword(password); } return r; }else { return null; } } catch (SQLException throwables) { throwables.printStackTrace(); } return null; } }
登入校验
package com.hqyj.cl.jdbc.book; public class Reader { private String user; private String password; public String getUser() { return user; } public void setUser(String user) { this.user = user; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "Reader{" + "user='" + user + '\'' + ", password='" + password + '\'' + '}'; } }
控制台
package com.hqyj.cl.jdbc.book; import com.hqyj.cl.jdbc.day02.dao.UserDao; import com.hqyj.cl.jdbc.day02.dao.daoImpl.UserDaoImpl; import java.util.List; import java.util.Scanner; public class UserDaoTest { public static void main(String[] args) { Scanner sc = new Scanner(System.in); UserDaoBook userDao = new UserDaoBookImpl(); System.out.println("请输入用户名和密码"); Reader r = userDao.login(sc.next(),sc.next()); if(r!=null){ while (true){ System.out.println("------选择菜单--------\n-----1.增加图书-----\n-----2.修改图书-----\n-----3.删除图书-----\n-----4.查询图书-----\n-----5.查询图书列表-----\n-----6.退出系统-----"); int a = sc.nextInt(); switch(a){ case 1: System.out.println("请依次输入 书名 作者 价格 出品国家"); userDao.addUser(sc.next(),sc.next(),sc.nextDouble(),sc.next()); break; case 2: System.out.println("请输入要修改价格的书名 以及要修改后的价格"); userDao.revisePrice(sc.next(),sc.nextDouble()); break; case 3: System.out.println("请输入要删除图书的书名"); userDao.delete(sc.next()); break; case 4: System.out.println("请输入要查询图书书名的关键字"); List<User> users = userDao.dimQuery(sc.next()); System.out.println(users); break; case 5: System.out.println("所有图书信息如下\n"); System.out.println(userDao.queryAll()); break; case 6: System.out.println("bye-"); System.exit(0); break; default: System.out.println("指令错误,请重新输入"); } } }else{ System.out.println("用户名或者密码错误"); System.exit(0); } } }