java 操作mysql数据库

package cn.studio.domain;

import java.sql.Date;

public class User {
 private String idcard;
 private String username;
 private String password;
 private Date birthday;
 private String sex;
 private String email;

 public User() {
 }

 public User(String idcard, String username, String password, Date birthday,
   String sex, String email) {
  super();
  this.idcard = idcard;
  this.username = username;
  this.password = password;
  this.birthday = birthday;
  this.sex = sex;
  this.email = email;
 }

 public String getIdcard() {
  return idcard;
 }

 public void setIdcard(String idcard) {
  this.idcard = idcard;
 }

 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 Date getBirthday() {
  return birthday;
 }

 public void setBirthday(Date birthday) {
  this.birthday = birthday;
 }

 public String getSex() {
  return sex;
 }

 public void setSex(String sex) {
  this.sex = sex;
 }

 public String getEmail() {
  return email;
 }

 public void setEmail(String email) {
  this.email = email;
 }

}

 

 

 

package cn.studio.dao;

import cn.studio.domain.User;

public interface UserDao {
 // 与数据库建立连接
 public void getConnction();

//获取所有用户

public List<User> getAll();

 // 保存用户
 public User add(User user);

 // 用身份证来查找用户
 public User findUserByIdcard(String idcard);

 // 删除用户
 public void deleteUser(String idcard);

 // 更新用户
 public User updateUser(User user);
}

 

package cn.studio.dao.impl;

import java.sql.*;

import cn.studio.dao.UserDao;
import cn.studio.domain.User;

public class UserDaoImpl implements UserDao {
 public static final String USERNAME = "root";
 public static final String USERPASS = "3306";
 Connection connection = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;

 

public List<User> getAll() {
  List<User> userList = new LinkedList<User>();
  String selectStr = "select *from province";
  try {
   pstmt = connection.prepareStatement(selectStr);
   rs = pstmt.executeQuery();
   while (rs.next()) {
    User u = new User();
    u.setUsername(rs.getString(1));
    u.setPassword(rs.getString(2));
    u.setIdcard(rs.getString(3));
    u.setEmail(rs.getString(4));
    u.setBirthday(rs.getDate(5));
    u.setSex(rs.getString(6));
    userList.add(u);
   }
   return userList;
  } catch (SQLException e) {
   e.printStackTrace();
   return null;
  }

 }

 

 // 添加用户
 public User add(User user1) {
  String insertStr = "insert into user values(?,?,?,?,?,?)";
  try {

   pstmt = connection.prepareStatement(insertStr);
   pstmt.setString(1, user1.getUsername());
   pstmt.setString(2, user1.getPassword());
   pstmt.setString(3, user1.getIdcard());
   pstmt.setString(4, user1.getEmail());
   pstmt.setDate(5, user1.getBirthday());
   pstmt.setString(6, user1.getSex());
   pstmt.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return user1;
 }

 // 根据身份证来查找用户

 public User findUserByIdcard(String idcard) {
  User user = new User();
  String queryStr = "select * from user where idcard=?";
  try {
   pstmt = connection.prepareStatement(queryStr);
   pstmt.setString(1, idcard);
   rs = pstmt.executeQuery();
   while (rs.next()) {
    user.setUsername(rs.getString(1));
    user.setPassword(rs.getString(2));
    user.setIdcard(rs.getString(3));
    user.setEmail(rs.getString(4));
    user.setBirthday(rs.getDate(5));
    user.setSex(rs.getString(6));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return user;

 }

 // 删除指定身份证的用户
 public void deleteUser(String id) {
  String deleteStr = "delete from user where idcard=?";
  try {
   pstmt = connection.prepareStatement(deleteStr);
   pstmt.setString(1, id);
   pstmt.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  }

 }

 // 更新用户

 public User updateUser(User user) {
  String updateStr = "update user set username=?,email=?,birthday=?where idcard='430381198911208138'";
  try {
   pstmt = connection.prepareStatement(updateStr);
   pstmt.setString(1, user.getUsername());
   pstmt.setString(2, user.getEmail());
   pstmt.setDate(3, new Date(1989 - 11 - 20));
   pstmt.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return user;
 }

 // 连接数据库
 public void getConnction() {
  try {
   Class.forName("com.mysql.jdbc.Driver");

   connection = DriverManager.getConnection(
     "jdbc:mysql://localhost:3306/demo", USERNAME, USERPASS);

  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

 // 关闭数据库
 public void closeConnection() {
  if (!(rs == null)) {
   try {
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if (!(pstmt == null)) {
   try {
    pstmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if (!(connection == null)) {
   try {
    connection.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }

 }

}

 

 

import java.sql.Date;

import cn.studio.dao.impl.UserDaoImpl;
import cn.studio.domain.User;

public class Main {

 public static void main(String[] args) {
  UserDaoImpl u = new UserDaoImpl();
  User user = new User("430381198911208138", "11111111", "11111111",
    new Date(1989 - 12 - 01), "男", "2222222@163.com");

  u.getConnction();
  String idcard = "430381198911208138";
  u.deleteUser(idcard);
  u.add(user);

  System.out.println(u.findUserByIdcard(idcard).getBirthday());

  user.setUsername("刘轶材");
  user.setEmail("lyc15115672844@gmail.com");
  user.setBirthday(new Date(1989 - 11 - 20));
  u.updateUser(user);
  u.closeConnection();

 }
}