package 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;
import javabean.User;
import utils.JDBCUtils;
public class UsersDao {
//增加数据
public boolean insert(User user){
Connection con=null;
Statement state=null;
try {
con=JDBCUtils.getCon();
state=con.createStatement();
java.util.Date birthday=user.getBirthDay();
String sqlBirthday=String.format("%tF", birthday);
String sql="insert into user(id,username,password,email,birthday) "
+ "values('"+user.getId()+"','"
+user.getUsername()+"','"
+user.getPassword()+"','"
+user.getEmail()+"','"
+sqlBirthday+"')";
int row=state.executeUpdate(sql);
if(row>0){
return true;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.realse(null,state,con);
}
return false;
}
//查询全部数据
public List<User> findAllUser(){
Connection con=null;
Statement state=null;
ResultSet rs=null;
try {
con=JDBCUtils.getCon();
state=con.createStatement();
String sql="select * from user";
rs=state.executeQuery(sql);
List<User> list=new ArrayList<User>();
while(rs.next()){
User user=new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
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) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.realse(rs,state,con);
}
return null;
}
//根据ID查询记录
public User findById(int id){
Connection con=null;
PreparedStatement prs=null;
ResultSet rs=null;
try {
con=JDBCUtils.getCon();
String sql="select * from user where id=?";
prs=con.prepareStatement(sql);
prs.setInt(1, id);
rs=prs.executeQuery();
if(rs.next()){
User user=new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
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) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.realse(rs,prs,con);
}
return null;
}
//根据ID修改
public boolean update(User user){
Connection con=null;
PreparedStatement prs=null;
try {
con=JDBCUtils.getCon();
String sql="update user set username=?,password=? where id=?";
prs=con.prepareStatement(sql);
prs.setString(1, user.getUsername());
prs.setString(2, user.getPassword());
prs.setInt(3, user.getId());
int row=prs.executeUpdate();
if(row>0){
return true;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.realse(null,prs,con);
}
return false;
}
//删除根据Id
public boolean deleteById(int id){
Connection con=null;
PreparedStatement prs=null;
try {
con=JDBCUtils.getCon();
String sql="delete from user where id=?";
prs=con.prepareStatement(sql);
prs.setInt(1, id);
int row=prs.executeUpdate();
if(row>0){
return true;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.realse(null,prs,con);
}
return false;
}
}
复制代码
复制代码
package javabean;
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;
}
}
复制代码
复制代码
package utils;
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(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","root");
return con;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//关闭连接
public static void realse(ResultSet rs,Statement state,Connection con){
try {
if(rs !=null){
rs.close();
}
if(state !=null){
state.close();
}
if(con !=null){
con.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
复制代码
复制代码
package Test;
import java.util.Date;
import dao.UsersDao;
import javabean.User;
public class InsTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
User user=new User();
user.setId(1);
user.setUsername("zhaoqian");
user.setPassword("123456");
user.setEmail("1558938514@qq.com");
user.setBirthDay(new Date());
System.out.println(dao.insert(user));
}
}
复制代码
复制代码
package Test;
import java.util.ArrayList;
import java.util.List;
import dao.UsersDao;
import javabean.User;
public class ShowTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
List<User> list=dao.findAllUser();
for(int i=0;i<list.size();i++){
System.out.println(list.get(i));
}
}
}
复制代码
复制代码
package Test;
import dao.UsersDao;
import javabean.User;
public class ShowOneTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
User u=dao.findById(1);
System.out.println(u.getUsername());
}
}
复制代码
复制代码
package Test;
import dao.UsersDao;
import javabean.User;
public class UpdateTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
User user=new User();
user.setId(1);
user.setUsername("zhangsan");
user.setPassword("zhangsan123");
System.out.println(dao.update(user));
}
}
复制代码
复制代码
package Test;
import dao.UsersDao;
public class DelectTest {
public static void main(String[] args) {
UsersDao dao=new UsersDao();
System.out.println(dao.deleteById(1));
}
}
