jsp第六次作业
USE test; CREATE TABLE lg( id INT(10), sname VARCHAR(10), spassword VARCHAR(10) );
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;
}
}
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;
}
}
}
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;
}
}
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操作步骤
// 注册学生信息
}
}
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());
}
}
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());
}
}
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则成功，否则不成功
}
}
public class test5 {
public static void main(String[] args){
//测试5删除信息代码
Dao dao= new Dao();
boolean flag = dao.delete(2);
System.out.println(flag);
}
}
浙公网安备 33010602011771号