jsp第六次作业
SQL
USE test; CREATE TABLE lg( id INT(10), sname VARCHAR(10), spassword VARCHAR(10) );
login
public class login { private int id; private String sname; private String spassword; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public String getSpassword() { return spassword; } public void setSpassword(String spassword) { this.spassword = spassword; } }
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/test", "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; } } }
Dao
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 Dao { // 1.提供添加方法 public boolean insert(login lg) { Connection con = null; Statement stmt = null; try { //1.获取连接对象 con=JDBCUtils.getCon(); //2.获取执行sql语句的对象 stmt=con.createStatement(); //3.执行sql语句(添加信息语句) String sql="insert into lg(id,sname,spassword)"+"values('" +lg.getId()+"','" +lg.getSname()+"','" +lg.getSpassword()+"'"//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<login>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 lg"; rs = stmt.executeQuery(sql); //4.遍历rs List<login> list = new ArrayList<login>(); while(rs.next()){ //一行数据对应一个对象，获取每一行的对象，就设置给一个user对象 login lg=new login(); lg.setId(rs.getInt("id")); lg.setSname(rs.getString("sname")); lg.setSpassword(rs.getString("spassword")); list.add(lg); } return list; }catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils.realse(rs, stmt, con); } // return null; } //3.根据id，来查询记录 public login findUserById(int id){ Connection con= null; PreparedStatement stmt = null; ResultSet rs = null; try{ //1.获取连接对象 con = JDBCUtils.getCon(); //2.获取执行sql语句的对象 String sql ="select * from lg where id=?"; stmt = con.prepareStatement(sql); //3.执行sql语句,给id赋值 stmt.setInt(1, id); rs = stmt.executeQuery(); //4.遍历rs if(rs.next()){ //一行数据对应一个对象，获取每一行的对象，就设置给一个user对象 login lg=new login(); lg.setId(rs.getInt("id")); lg.setSname(rs.getString("sname")); lg.setSpassword(rs.getString("spassword")); return lg; } }catch (Exception e) { throw new RuntimeException(e); } finally { JDBCUtils.realse(rs, stmt, con); } return null; } //4.提供一个修改方法，根据id值修改记录 public boolean update(login lg){ Connection con = null; PreparedStatement stmt = null; try { //1.获取连接对象 con=JDBCUtils.getCon(); //2.获取执行sql语句的对象 String sql="update lg set sname =?,spassword=? where id=?"; stmt = con.prepareStatement(sql);//2.的sql语句* //3.执行sql语句(给占位符赋值) stmt.setString(1, lg.getSname()); stmt.setString(2, lg.getSpassword()); stmt.setInt(3, lg.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 lg 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; } }
test(添加)
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class test { public static void main(String[] args) { // 测试1插入信息代码 Dao dao = new Dao(); login lg = new login(); lg.setId(2); lg.setSname("zqb"); lg.setSpassword("1111"); boolean flag = dao.insert(lg); System.out.print(flag); // jdbc操作步骤 // 注册学生信息 } }
test2(查看)
import java.util.List; public class test2 { public static void main(String[] args){ //测试2查询所有信息条数代码 Dao dao= new Dao(); List<login> list =dao.findAllUser(); System.out.println(list.size()); } }
test3(按id查)
public class test3 { public static void main(String[] args){ //测试3查询id=？的名字代码 Dao dao= new Dao(); login lg = dao.findUserById(2); System.out.println(lg.getSname()); } }
test4(修改)
public class test4 { public static void main(String[] args){ //测试4修改信息代码 Dao dao= new Dao(); login lg=new login(); lg.setId(2); lg.setSname("zqbb"); lg.setSpassword("2222"); boolean flag = dao.update(lg); System.out.println(flag); //true则成功，否则不成功 } }
test5(删)
public class test5 { public static void main(String[] args){ //测试5删除信息代码 Dao dao= new Dao(); boolean flag = dao.delete(2); System.out.println(flag); } }
