JSP第六次作业
create database diyige;
use diyige;
create table diyige (id int primary key auto_increment,
name varchar(30),
password varchar(30),
email varchar(100),
birthday Date);
insert into diyige(name,password,email,birthday) values('tianyihui','8302','5288471512@qq.com','1999-02-14');
insert into diyige(name,password,email,birthday) values('xiaotao','6384','528765456@qq.com','1999-02-14');
insert into diyige(name,password,email,birthday) values('minkexin','9872','853308091@qq.com','1999-01-26');
package cn.itcast.jdbc.example;
import java.util.Date;
public class Users {
//封装
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;
}
}
package cn.itcast.jdbc.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
//获取连接对象的方法
public static Connection getCon() throws Exception{
//1.注册和加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取驱动
Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/diyige", "root", "root");
return con;
}
//关闭连接，释放资源
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();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package cn.itcast.jdbc.example;
import java.util.Date;
public class JdbcInsertTest {
public static void main(String[] args) {
//插入数据
UsersDao dao=new UsersDao();
Users user1=new Users();
user1.setUsername("sui");
user1.setPassword("8437");
user1.setEmail("52884712@qq.com");
user1.setBirthDay(new Date());
boolean flag=dao.insert(user1);
System.out.println(flag);
}
}
package cn.itcast.jdbc.example;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class UsersDao {
/**
* 完成对数据库的crud操作
* @param args
*/
public boolean insert(Users user1){
Connection con=null;
Statement stmt=null;
try{
//1.获取连接对象
con=JDBCUtils.getCon();
//2.获取执行sql语句的对象
stmt = con.createStatement();
//3.执行sql
java.util.Date birthday=user1.getBirthDay();
String sqlBirthDay=String.format("%tF", birthday);
String sql="insert into diyige(id,name,password,email,birthday)"+"values('"
+user1.getId()+"','"
+user1.getUsername()+"','"
+user1.getPassword()+"','"
+user1.getEmail()+"','"
+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<Users> 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 diyige";
rs=stmt.executeQuery(sql);
//4.遍历rs
List<Users> list=new ArrayList<Users>();
while(rs.next()){
//一行数据对应一个对象
Users user1=new Users();
user1.setId(rs.getInt("id"));
user1.setUsername(rs.getString("name"));
user1.setPassword(rs.getString("password"));
user1.setEmail(rs.getString("email"));
java.sql.Date birthday=rs.getDate("birthday");
user1.setBirthDay(birthday);
//把对象添加到集合中
list.add(user1);
}
return list;
}catch(Exception e){
throw new RuntimeException(e);
}finally{
JDBCUtils.realse(rs, stmt, con);
}
}
//3.根据id，来查询记录
public Users findUserById(int id){
Connection con =null;
PreparedStatement stmt=null;
ResultSet rs =null;
try{
//1.获取连接对象
con=JDBCUtils.getCon();
//2.获取执行sql语句的对象
String sql="select * from diyige where id=?";
stmt=con.prepareStatement(sql);
//3.执行sql,给占位符赋值
stmt.setInt(1, id);
rs=stmt.executeQuery();
//4.遍历rs
if(rs.next()){
//一行数据对应一个对象
Users user1=new Users();
user1.setId(rs.getInt("id"));
user1.setUsername(rs.getString("name"));
user1.setPassword(rs.getString("password"));
user1.setEmail(rs.getString("email"));
java.sql.Date birthday=rs.getDate("birthday");
user1.setBirthDay(birthday);
return user1;
}
}catch(Exception e){
throw new RuntimeException(e);
}finally{
JDBCUtils.realse(rs, stmt, con);
}
return null;
}
//4.提供一个修改方法，根据id修改记录
public boolean update(Users user1){
Connection con=null;
PreparedStatement stmt=null;
try{
//1.获取连接对象
con=JDBCUtils.getCon();
//2.获取执行sql语句的对象
String sql="update diyige set name=?,password=? where id=?";
stmt = con.prepareStatement(sql);
//3.执行sql,给占位符赋值
stmt.setString(1, user1.getUsername());
stmt.setString(2, user1.getPassword());
stmt.setInt(3, user1.getId());
int row=stmt.executeUpdate();
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 diyige where id=?";
stmt = con.prepareStatement(sql);
//3.执行sql,给占位符赋值
stmt.setInt(1,id);
int row=stmt.executeUpdate();
if(row>0){
//插入成功
return true;
}
}catch(Exception e){
throw new RuntimeException(e);
}finally{
JDBCUtils.realse(null, stmt, con);
}
return false;
}
}
package cn.itcast.jdbc.example;
import java.util.Date;
public class JdbcInsertTest {
public static void main(String[] args) {
//插入数据
UsersDao dao=new UsersDao();
Users user1=new Users();
user1.setUsername("sui");
user1.setPassword("8437");
user1.setEmail("52884712@qq.com");
user1.setBirthDay(new Date());
boolean flag=dao.insert(user1);
System.out.println(flag);
}
}
package cn.itcast.jdbc.example;
import java.util.List;
public class FindAllUserTest {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
UsersDao dao = new UsersDao();
List<Users> list=dao.findAllUser();
System.out.println(list.size());
}
}
package cn.itcast.jdbc.example;
public class FindUserByIdTest {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
UsersDao dao = new UsersDao();
Users u=dao.findUserById(4);
System.out.println(u.getUsername());
}
}
package cn.itcast.jdbc.example;
public class UpdateUserTest {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
UsersDao dao=new UsersDao();
Users u=new Users();
u.setId(4);
u.setUsername("suixiaoyi");
u.setPassword("4829");
boolean flag=dao.update(u);
System.out.println(flag);
}
}
package cn.itcast.jdbc.example;
public class DeleteUserTest {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
UsersDao dao=new UsersDao();
boolean flag=dao.delete(1);
System.out.println(flag);
}
}
