第六个JSP作业-jdbc
创建数据库
1.创建javaBean的封装数据：User
package com.test.jdbc;
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;
}
}
2.创建工具类：JDBCUtils
package com.test.jdbc;
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{
Class.forName("com.mysql.jdbc.Driver");
Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "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();
}
}
}
}
3.创建一个Dao类：UsersDao
package com.test.jdbc;
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 {
public boolean insert(User user1){
Connection con=null;
Statement stmt=null;
try{
con=JDBCUtils.getCon();
stmt = con.createStatement();
java.util.Date birthday=user1.getBirthDay();
String sqlBirthDay=String.format("%tF", birthday);
String sql="insert into user(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;
}
public List<User> findAllUser(){
Connection con =null;
Statement stmt =null;
ResultSet rs =null;
try{
con=JDBCUtils.getCon();
stmt=con.createStatement();
String sql="select * from user";
rs=stmt.executeQuery(sql);
List<User> list=new ArrayList<User>();
while(rs.next()){
User user1=new User();
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);
}
}
public User findUserById(int id){
Connection con =null;
PreparedStatement stmt=null;
ResultSet rs =null;
try{
con=JDBCUtils.getCon();
String sql="select * from user where id=?";
stmt=con.prepareStatement(sql);
stmt.setInt(1, id);
rs=stmt.executeQuery();
if(rs.next()){
User user1=new User();
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;
}
public boolean update(User user1){
Connection con=null;
PreparedStatement stmt=null;
try{
con=JDBCUtils.getCon();
String sql="update user set name=?,password=? where id=?";
stmt = con.prepareStatement(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;
}
public boolean delete (int id){
Connection con=null;
PreparedStatement stmt=null;
try{
con=JDBCUtils.getCon();
String sql="delete from user where id=?";
stmt = con.prepareStatement(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;
}
}
4.创建的测试类
1.测试添加方法类：JdbcInsertTest
package com.test.jdbc;
import java.util.Date;
public class JdbcInsertTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
User user1=new User();
user1.setId(4);
user1.setUsername("mkx");
user1.setPassword("123321");
user1.setEmail("123321@qq.com");
user1.setBirthDay(new Date());
boolean flag=dao.insert(user1);
}
}
2.测试查询所有的方法类：FindAllUserTest
package com.test.jdbc;
import java.util.List;
public class FindAllUserTest {
public static void main(String[] args) {
// TODO Auto-generated method stub
UsersDao dao = new UsersDao();
List<User> list=dao.findAllUser();
System.out.println(list.size());
}
3.测试查询一个对象的类：FindUserByIdTest
package com.test.jdbc;
public class FindUserByIdTest {
public static void main(String[] args) {
UsersDao dao = new UsersDao();
User u=dao.findUserById(4);
System.out.println(u.getUsername());
}
}
4.测试修改方法的类：UpdateUserTest
package com.test.jdbc;
public class UpdateUserTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
User u=new User();
u.setId(4);
u.setUsername("sq");
u.setPassword("123345");
boolean flag=dao.update(u);
System.out.println(flag);
}
}
5.测试删除方法的类：DeleteUserTest
package com.test.jdbc;
public class DeleteUserTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
boolean flag=dao.delete(1);
System.out.println(flag);
}
}
