java 封装工具类_JAVA学习笔记(五十一)- DBUtil 封装数据库工具类

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.List;

public class Test04 {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

// 根据编号查询用户信息

public User getUserById(int id) {

User user = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/test", "root", "123456");

/* * stmt = conn.createStatement(); String sql = * "select * from user where id=" + id; */

String sql = "select * from user where id=?";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, id);

rs = pstmt.executeQuery();

if (rs.next()) {

user = new User(rs.getInt("id"), rs.getString("name"),

rs.getString("password"), rs.getInt("age"),

rs.getDate("birthday"));

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

rs.close();

pstmt.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

rs = null;

pstmt = null;

conn = null;

}

}

return user;

}

// 检查用户登陆，即判断用户名或密码是否正确

public boolean checkLogin(User user) {

boolean flag = false;

/*String sql = "select * from user where name='" + user.getName() + "' and password='" + user.getPassword() + "'";*/

String sql="select * from user where name=? and password=?";

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/test", "root", "123456");

pstmt=conn.prepareStatement(sql);

pstmt.setString(1, user.getName());

pstmt.setString(2, user.getPassword());

rs=pstmt.executeQuery();

if(rs.next()){

flag=true;

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}finally{

try {

rs.close();

pstmt.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

return flag;

}

//查询所有用户信息

public List getAllUser(){

List users=new ArrayList();

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/test", "root", "123456");

String sql = "select * from user";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

while(rs.next()) {

users.add(new User(rs.getInt("id"), rs.getString("name"),

rs.getString("password"), rs.getInt("age"),

rs.getDate("birthday")));

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

rs.close();

pstmt.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

rs = null;

pstmt = null;

conn = null;

}

}

return users;

}

//用户注册，即添加用户

public void addUser(User user){

String sql="insert into user values (null,?,?,?,?)";

//调用工具类，获取连接Connection

conn=DBUtil.getConnection();

try {

pstmt=conn.prepareStatement(sql);

pstmt.setString(1, user.getName());

pstmt.setString(2,user.getPassword());

pstmt.setInt(3, user.getAge());

pstmt.setDate(4, new Date(user.getBirthday().getTime()));//将java.util.Date转换为java.sql.Date

pstmt.executeUpdate();

System.out.println("添加用户成功！");

} catch (SQLException e) {

e.printStackTrace();

}finally{

DBUtil.closeAll(rs, pstmt, conn);

}

}

//根据编号删除用户信息

public void deleteUserById(int id){

}

//更新用户信息

public void updateUser(User user){

}

//根据编号范围和姓名模糊查询

public List getUserByCondition(int sid,int eid,String name){

return null;

}

public static void main(String[] args) {

Test04 test = new Test04();

User user = test.getUserById(2);

System.out.println(user);

User user=new User("赵超","222222");

boolean flag=test.checkLogin(user);

System.out.println("是否登陆成功？"+flag);

List users=test.getAllUser();

System.out.println(users);

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");

java.util.Date birthday=null;

try {

birthday = sdf.parse("2014-2-14");

} catch (ParseException e) {

e.printStackTrace();

}

test.addUser(new User("余书石", "123456", 18, birthday));

}

}