package cn.itcast.jdbc.example.dao;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import cn.itcat.idbc.example.domain.JDBCUtills;
import cn.itcat.idbc.example.domain.User;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
public class UsersDao {
public boolean insert (User user){
Connection conn =null;
Statement stmt =null;
ResultSet rs = null;
try{
conn = JDBCUtills.getConnection();
stmt = (Statement) conn.createStatement();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.getBirthday());
String sql ="INSERT INTO users (id,name,password,email,birthday)"+"VALUES ("
+user.getId()+",'"+user.getName()+"','"+user.getPassword()+"','"+user.getEmail()+"','"+birthday+"')";
int num =stmt.executeUpdate(sql);
if (num >0){
return true;
}
return false;
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtills.release(rs, stmt, conn);
}
return false;
}
//查找所有User对象
public ArrayList findall(){
Connection conn =null;
Statement stmt =null;
ResultSet rs = null;
ArrayList list = new ArrayList();
try{
conn = JDBCUtills.getConnection();
stmt = (Statement) conn.createStatement();
String sql = "SELECT * FROM users";
rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setEmail(rs.getString("email"));
user.setName(rs.getString("name"));
user.setBirthday(rs.getDate("birthday"));
user.setPassword(rs.getString("password"));
list.add(user);
}
return list;
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtills.release(rs, stmt, conn);
}
return null;
}
//根据id查找指定的user
public User find (int id){
Connection conn =null;
Statement stmt =null;
ResultSet rs = null;
try{
conn = JDBCUtills.getConnection();
stmt = (Statement) conn.createStatement();
String sql ="SELECt * FROM users WHERe id="+id;
rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setEmail(rs.getString("email"));
user.setName(rs.getString("name"));
user.setBirthday(rs.getDate("birthday"));
user.setPassword(rs.getString("password"));
return user;
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtills.release(rs, stmt, conn);
}
return null;
}
//删除用户
public boolean delete(int id){
Connection conn =null;
Statement stmt =null;
ResultSet rs = null;
try{
conn = JDBCUtills.getConnection();
stmt = (Statement) conn.createStatement();
String sql ="SELECT * FROM users WHERe id="+id;
int num =stmt.executeUpdate(sql);
if(num>0){
return true;
}
return false;
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtills.release(rs, stmt, conn);
}
return false;
}
//修改用户
public boolean update(User user){
Connection conn =null;
Statement stmt =null;
ResultSet rs = null;
try{
conn = JDBCUtills.getConnection();
stmt = (Statement) conn.createStatement();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.getBirthday());
String sql ="INSERT INTO users (id,name,password,email,birthday)"+"VALUES ("
+user.getId()+",'"+user.getName()+"','"+user.getPassword()+"','"+user.getEmail()+"','"+birthday+"')";
int num =stmt.executeUpdate(sql);
if (num >0){
return true;
}
return false;
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtills.release(rs, stmt, conn);
}
return false;
}
}
JDBCUtils
package cn.itcat.idbc.example.domain;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
public class JDBCUtills {
public static Connection getConnection() throws SQLException,ClassNotFoundException{
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306ebc";
String username = "root";
String password = "";
Connection conn = (Connection) DriverManager.getConnection(url,username,password);
return conn;
}
public static void release(ResultSet rs,Statement stmt,Connection conn){
if(stmt !=null){
try{
stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
stmt=null;
}
if(conn !=null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
conn=null;
}
if(rs !=null){
try{
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
rs=null;
}
}
}
建立数据库（MySQL）
package cn.itcat.idbc.example.domain;
import java.util.ArrayList;
import java.util.Date;
public class User {
private int id;
private String name;
private String password;
private String email;
private Date birthday;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
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;
}
}
Example01
#建立数据库连接获取账号和密码
#下载mysql-connerctor-java-5.0.0-bin包导入一下
#mysql和MyEclipse都有破解版想下载的话私聊发
package databaseconnection;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Example01 {
public static void main(String[] args) throws SQLException{
Statement stmt = null;
ResultSet rs =null;
Connection conn = null;
try{
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306ebc";
String username = "root";
String password = "";
conn = DriverManager.getConnection(url,username,password);
stmt = conn.createStatement();
String sql = "select * from users";
rs = stmt.executeQuery(sql);
System.out.println("id | name | password | email | birthday");
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String psw = rs.getString("password");
String email = rs.getString("email");
Date birthday = rs.getDate("birthday");
System.out.println(id +" | " +name + "| "+psw + " | "+email + " | " + birthday);
}
}catch (ClassNotFoundException e){
e.printStackTrace();
}finally{
if(rs!=null){
try{
rs.close();
}catch (SQLException e){
e.printStackTrace();
}
rs = null;
}
if(stmt!=null){
try{
stmt.close();
}catch (SQLException e){
e.printStackTrace();
}
stmt = null;
}
if(conn!=null){
try{
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
conn = null;
}
}
}
}
