java基础笔记（9）
通过JDBC像数据库实现CRUD操作，这里通过一个存储查看人员的案例来了解java是如何通过JDBC实现与数据库的连接，三层结构中的模型层（数据访问），控制层（业务逻辑）、以及视图层（表示层）又是怎么进行一个交互实现业务需求的。
模型层：
package com.jdbc; import java.util.Date; public class laoren { private Integer id; private String username; private Integer sex; private Integer age; private String hobby; private String create_user; private Date create_date; private String update_user; private Date update_date; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public Integer getSex() { return sex; } public void setSex(Integer sex) { this.sex = sex; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getHobby() { return hobby; } public void setHobby(String hobby) { this.hobby = hobby; } public String getCreate_user() { return create_user; } public void setCreate_user(String create_user) { this.create_user = create_user; } public Date getCreate_date() { return create_date; } public void setCreate_date(Date create_date) { this.create_date = create_date; } public String getUpdate_user() { return update_user; } public void setUpdate_user(String update_user) { this.update_user = update_user; } public Date getUpdate_date() { return update_date; } public void setUpdate_date(Date update_date) { this.update_date = update_date; } @Override public String toString() { return "laoren [id=" + id + ", username=" + username + ", sex=" + sex + ", age=" + age + ", hobby=" + hobby + ", create_user=" + create_user + ", create_date=" + create_date + ", update_user=" + update_user + ", update_date=" + update_date + "]"; } }
模型实现增删查改：
package com.jdbc; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.jdbc.DBUtil; public class laorenDao { public void addLaoren(laoren l) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "insert into laoren "+"(username,sex,age,hobby,create_user,create_date,update_user,update_date)"+ "values(?,?,?,?,?,current_date(),?,current_date())"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, l.getUsername()); ptmt.setLong(2, l.getSex()); ptmt.setLong(3, l.getAge()); ptmt.setString(4, l.getHobby()); ptmt.setString(5, l.getCreate_user()); ptmt.setString(6, l.getUpdate_user()); ptmt.execute(); } public void delLaoren(Integer id) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "delete from laoren "+ " where id = ?"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setLong(1, id); ptmt.execute(); } public List<laoren> query() throws SQLException{ Connection conn = DBUtil.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from laoren"); List<laoren> lr = new ArrayList<laoren>(); laoren l = null; while(rs.next()){ l = new laoren(); l.setUsername(rs.getString("username")); l.setId(rs.getInt("id")); l.setAge(rs.getInt("age")); l.setHobby(rs.getString("hobby")); l.setCreate_user(rs.getString("create_user")); l.setCreate_date(rs.getDate("create_date")); l.setUpdate_user(rs.getString("update_user")); l.setUpdate_date(rs.getDate("update_date")); lr.add(l); } return lr; } public laoren getlaoren(Integer id) throws SQLException { laoren l = null; Connection conn = DBUtil.getConnection(); String sql = "select * from laoren"+ " where id = ?"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setLong(1, id); ResultSet rs = ptmt.executeQuery(); while(rs.next()) { l = new laoren(); l.setId(rs.getInt("id")); l.setUsername(rs.getString("username")); l.setAge(rs.getInt("age")); l.setHobby(rs.getString("hobby")); l.setCreate_user(rs.getString("create_user")); l.setCreate_date(rs.getDate("create_date")); l.setUpdate_user(rs.getString("update_user")); l.setUpdate_date(rs.getDate("update_date")); } return l; } public laoren getlaoren(String username) throws SQLException { laoren l = null; Connection conn = DBUtil.getConnection(); String sql = "select * from laoren"+ " where username = ?"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, username); ResultSet rs = ptmt.executeQuery(); while(rs.next()) { l = new laoren(); l.setId(rs.getInt("id")); l.setUsername(rs.getString("username")); l.setAge(rs.getInt("age")); l.setHobby(rs.getString("hobby")); l.setCreate_user(rs.getString("create_user")); l.setCreate_date(rs.getDate("create_date")); l.setUpdate_user(rs.getString("update_user")); l.setUpdate_date(rs.getDate("update_date")); } return l; } public void updateLaoren(laoren l) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "update laoren "+" set username=?,sex=?,age=?,hobby=?,update_user=?,update_date=current_date()"+ " where id = ?"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, l.getUsername()); ptmt.setLong(2, l.getSex()); ptmt.setLong(3, l.getAge()); ptmt.setString(4, l.getHobby()); ptmt.setString(5, l.getUpdate_user()); ptmt.setLong(6, l.getId()); ptmt.execute(); } }
注：这里的数据库连接通过一个自定好的数据库工具类来实现：
package com.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBUtil { private static final String URL="jdbc:mysql://127.0.0.1:3306/laoguai?useUnicode=true&characterEncoding=utf8";//useUnicode表示允许使用自定义的Unicode,characterEncoding是给定自定义的Unicode是什么 private static final String user="root"; private static final String password="123456"; private static Connection conn = null; static { try { Class.forName("com.mysql.jdbc.Driver");//加载驱动程序 conn = DriverManager.getConnection(URL, user, password);//获得数据库的连接 } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static Connection getConnection() { return conn; } }
控制层：
package com.jdbc; import java.sql.SQLException; import java.util.List; import com.jdbc.*; public class laorenCon { public void add(laoren lr) throws SQLException{ laorenDao dao = new laorenDao(); dao.addLaoren(lr); } public void deit(laoren lr) throws SQLException{ laorenDao dao = new laorenDao(); dao.updateLaoren(lr); } public void del(Integer id) throws SQLException{ laorenDao dao = new laorenDao(); dao.delLaoren(id); } public void read(Integer id) throws SQLException{ laorenDao dao = new laorenDao(); System.out.println(dao.getlaoren(id).toString()); } public void read(String name) throws SQLException{ laorenDao dao = new laorenDao(); laoren y = dao.getlaoren(name); System.out.println(y.toString()); } public void readall() throws SQLException{ laorenDao dao = new laorenDao(); List<laoren> list = dao.query(); for(laoren y:list) { System.out.println(y.toString()); } } }
视图层；
package com.jdbc; import java.sql.SQLException; import java.util.Scanner; import java.util.regex.Pattern; public class view { private static final String CONTEXT = "欢迎来到功能选择区: \n"+ "下面是功能列表: \n"+ "[MAIN/M]:主菜单 \n"+ "[QUERY/Q]:查看捞人的全部信息 \n"+ "[GET/G]:查看某个捞人的信息（根据id或名字）\n"+ "[ADD/A]添加某个捞人 \n"+ "[UPDATE/U]:更新某个捞人的信息 \n"+ "[DELETE/D]:删除某个捞人的信息 \n"+ "[EXIT/E]:离开 \n"+ "[BREAK/B]:后退，返回功能选择区"; private static final String MAIN = "MAIN"; private static final String QUERY = "QUERY"; private static final String GET = "GET"; private static final String ADD = "ADD"; private static final String UPDATE = "UPDATE"; private static final String DELETE = "DELETE"; private static final String EXIT = "EXIT"; public static void main(String[] args) throws SQLException { System.out.println(CONTEXT); Scanner scan = new Scanner(System.in); laoren lr =new laoren(); laorenCon lc = new laorenCon(); String pren = null; Integer step = 1; while(scan.hasNext()) { String in = scan.next().toString(); if(EXIT.equals(in.toUpperCase())||EXIT.substring(0, 1).equals(in.toUpperCase())) { System.out.println("您已退出系统"); break; }else if(QUERY.equals(in.toUpperCase())||QUERY.substring(0, 1).equals(in.toUpperCase())) { lc.readall(); }else if(MAIN.equals(in.toUpperCase())||MAIN.substring(0, 1).equals(in.toUpperCase())) { System.out.println(CONTEXT); pren = null; step = 1; }else if(GET.equals(in.toUpperCase())||GET.substring(0, 1).equals(in.toUpperCase())||GET.equals(pren)) { //if(in.matches("\\d+")); pren = GET; if(step == 1) { System.out.println("请输入要查询的捞人名字或ID："); }else if (step == 2) { Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$"); if( pattern.matcher(in).matches()) lc.read(Integer.valueOf(in)); else { lc.read(in); } } if(GET.equals(pren)) { step++; } }else if(DELETE.equals(in.toUpperCase())||DELETE.substring(0, 1).equals(in.toUpperCase())||DELETE.equals(pren)) { //if(in.matches("\\d+")); pren = DELETE; if(step == 1) { System.out.println("请输入要删除的捞人的ID："); }else if (step == 2) { lc.del(Integer.valueOf(in)); } if(DELETE.equals(pren)) { step++; } }else if(ADD.equals(in.toUpperCase())||ADD.substring(0, 1).equals(in.toUpperCase())||ADD.equals(pren)) { pren = ADD; if(step == 1) { System.out.println("请输入新增捞人的姓名："); }else if(step == 2){ lr.setUsername(in); System.out.println("请输入新增捞人的年龄："); }else if(step == 3) { lr.setAge(Integer.valueOf(in)); System.out.println("请输入新增捞人的爱好："); }else if(step == 4) { lr.setHobby(in); System.out.println("请输入新增捞人的性别："); }else if(step == 5) { lr.setSex(Integer.valueOf(in)); System.out.println("请问这个捞人的创建人是谁："); }else if(step == 6) { lr.setCreate_user(in); System.out.println("请问这个捞人的更新人是谁："); }else if(step == 7) { lr.setUpdate_user(in); try { lc.add(lr); System.out.println("新增捞人成功"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); System.out.println("新增捞人失败"); } } if(ADD.equals(pren)) { step++; } }else if(UPDATE.equals(in.toUpperCase())||UPDATE.substring(0, 1).equals(in.toUpperCase())||UPDATE.equals(pren)) { pren = UPDATE; if(step == 1) { System.out.println("请输入更新捞人的姓名："); }else if(step == 2){ lr.setUsername(in); System.out.println("请输入更新捞人的年龄："); }else if(step == 3) { lr.setAge(Integer.valueOf(in)); System.out.println("请输入更新捞人的爱好："); }else if(step == 4) { lr.setHobby(in); System.out.println("请输入更新捞人的性别："); }else if(step == 5) { lr.setSex(Integer.valueOf(in)); System.out.println("请问更新这个捞人的更新人是谁："); }else if(step == 6) { lr.setUpdate_user(in); System.out.println("请问更新这个捞人ID是多少："); }else if(step == 7) { lr.setId(Integer.valueOf(in)); try { lc.deit(lr); System.out.println("更新捞人成功"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); System.out.println("更新捞人失败"); } } if(UPDATE.equals(pren)) { step++; } } else { System.out.println("您输入的值为："+in); } } } }
