Jsp第六次作业JDBC
UsersDao
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; /* 完成对数据库的增删改查（crud操作） */ public class UsersDao { // 1.提供添加方法 public boolean insert(User user) { Connection con = null; Statement stmt = null; try { //1.获取连接对象 con=JDBCUtils.getCon(); //2.获取执行sql语句的对象 stmt=con.createStatement(); //3.执行sql语句(添加信息语句) java.util.Date birthday=user.getBirthDay(); String sqlBirthDay=String.format("%tF",birthday);//把日期类型转换成字符串类型 String sql="insert into users(id,name,password,email,birthday)"+"values(" +user.getId()+",'" +user.getUsername()+"','" +user.getPassword()+"','" +user.getEmail()+"','" +sqlBirthDay+"'"//user.getBirthDay()替换成sqlBirthDay +")"; int row = stmt.executeUpdate(sql); if(row>0){ //插入成功 return true; } } catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils.realse(null, stmt, con); } return false; } //2.提供查询所有的方法 public List<User>findAllUser(){ Connection con= null; Statement stmt = null; ResultSet rs = null; try{ //1.获取连接对象 con = JDBCUtils.getCon(); //2.获取执行sql语句的对象 stmt = con.createStatement(); //3.执行sql语句(查询语句) String sql ="select * from users"; rs = stmt.executeQuery(sql); //4.遍历rs List<User> list = new ArrayList<User>(); while(rs.next()){ //一行数据对应一个对象，获取每一行的对象，就设置给一个user对象 User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); java.sql.Date birthday = rs.getDate("birthday"); user.setBirthDay(birthday);//子类把值传递给父类 //把对象添加到集合中 list.add(user); } return list; }catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils.realse(rs, stmt, con); } // return null; } //3.根据id，来查询记录 public User findUserById(int id){ Connection con= null; PreparedStatement stmt = null; ResultSet rs = null; try{ //1.获取连接对象 con = JDBCUtils.getCon(); //2.获取执行sql语句的对象 String sql ="select * from users where id=?"; stmt = con.prepareStatement(sql); //3.执行sql语句,给id赋值 stmt.setInt(1, id); rs = stmt.executeQuery(); //4.遍历rs if(rs.next()){ //一行数据对应一个对象，获取每一行的对象，就设置给一个user对象 User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); java.sql.Date birthday = rs.getDate("birthday"); user.setBirthDay(birthday);//子类把值传递给父类 return user; } }catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils.realse(rs, stmt, con); } return null; } //4.提供一个修改方法，根据id值修改记录 public boolean update(User user){ Connection con = null; PreparedStatement stmt = null; try { //1.获取连接对象 con=JDBCUtils.getCon(); //2.获取执行sql语句的对象 String sql="update users set name =?,password=? where id=?"; stmt = con.prepareStatement(sql);//2.的sql语句* //3.执行sql语句(给占位符赋值) stmt.setString(1, user.getUsername()); stmt.setString(2, user.getPassword()); stmt.setInt(3, user.getId()); int row = stmt.executeUpdate();//应该（）里无sql,因为在2.里已经传了sql语句* if(row>0){ //插入成功 return true; } } catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils.realse(null, stmt, con); } return false; } //5.删除方法 public boolean delete (int id){ Connection con = null; PreparedStatement stmt = null; try { //1.获取连接对象 con=JDBCUtils.getCon(); //2.获取执行sql语句的对象 String sql="delete from users where id=?"; stmt = con.prepareStatement(sql);//2.的sql语句* //3.执行sql语句(给占位符赋值) stmt.setInt(1,id); int row = stmt.executeUpdate();//应该（）里无sql,因为在2.里已经传了sql语句* if(row>0){ //插入成功 return true; } } catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils.realse(null, stmt, con); } return false; } }
JDBCUtils
import java.sql.Statement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCUtils { //获取连接对象的方法 public static Connection getCon() throws Exception{ //1.加载数据库驱动 Class.forName("com.mysql.jdbc.Driver"); //2.通过DriverManager获取数据库连接 Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root"); return con; //3.通过Connection対象获取Statement対象 } //关闭连接，释放资源 public static void realse(ResultSet rs,Statement stmt,Connection con){ if(rs!=null){ try{ rs.close(); }catch (SQLException e){ //TODO Auto-generated catch block e.printStackTrace(); } rs=null; } if(stmt!=null){ try{ stmt.close(); }catch (SQLException e){ //TODO Auto-generated catch block e.printStackTrace(); } stmt=null; } if(con!=null){ try{ con.close(); }catch (SQLException e){ e.printStackTrace(); } con=null; } } }
User
import java.util.Date; public class User { private int id; private String username; private String password; private String email; private Date birthDay; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Date getBirthDay() { return birthDay; } public void setBirthDay(Date birthDay) { this.birthDay = birthDay; } }
JdbcInsertTest
import java.util.Date; public class JdbcInsertTest { public static void main(String[] args){ //测试1插入信息代码 UsersDao dao= new UsersDao(); User user= new User(); user.setId(6); user.setUsername("gjm"); user.setPassword("789"); user.setEmail("gjm@qq.com"); user.setBirthDay(new Date(1998-07-17)); boolean flag=dao.insert(user); System.out.print(flag); } }
FindAllUserTest
import java.util.List; public class FindAllUserTest { public static void main(String[] args){ //测试2查询所有信息条数代码 UsersDao dao= new UsersDao(); List<User> list =dao.findAllUser(); System.out.println(list.size()); } }
FindUserByIdTest
public class FindUserByIdTest { public static void main(String[] args){ //测试3查询id=？的名字代码 UsersDao dao= new UsersDao(); User u = dao.findUserById(5); System.out.println(u.getUsername()); } }
UpdateUserTest
public class UpdateUserTest { public static void main(String[] args){ //测试4修改信息代码 UsersDao dao= new UsersDao(); User u = new User(); u.setId(4); u.setUsername("GJM"); u.setPassword("987"); boolean flag = dao.update(u); System.out.println(flag); //true则成功，否则不成功 } }
DeleteUserTest
public class DeleteUserTest { public static void main(String[] args){ //测试5删除信息代码 UsersDao dao= new UsersDao(); boolean flag = dao.delete(6); System.out.println(flag); } }
最终表
