- 创建连接数据库类的工具类JdbcUtils，里面是连接与释放数据库的方法
package com.tanghao.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
Properties properties = new Properties();
InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try{
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//加载驱动
Class.forName("driver");
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn = null;
try{
//创建连接,获得连接对象
conn = DriverManager.getConnection(url,username,password);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
// 释放
public static void free(ResultSet rs, Statement st,Connection conn){
try{
if (rs != null) rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try{
if (st != null) st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (conn != null) conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
- 创建JavaBean User类（里面有属性，get与set方法，构造方法）
package com.tanghao.pojo;
public class User {
private Integer id;
private String username;
private String password;
private String email;
public User(){
}
public User(String username, String password, String email){
this.username = username;
this.password = password;
this.email = email;
}
public User(Integer id, String username, String password, String email) {
this.id = id;
this.username = username;
this.password = password;
this.email = email;
}
public void setId(Integer id) {
this.id = id;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getId() {
return id;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
public String getEmail() {
return email;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + ''' +
", password='" + password + ''' +
", email='" + email + ''' +
'}';
}
}
- 创建UserDao(里面是操作数据库对象的一些方法)
package com.tanghao.dao;
import com.tanghao.pojo.User;
import com.tanghao.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
//向数据库中添加用户记录的方法add()
public void add(User user){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtils.getConnection();
String sql = "INSERT INTO t_user(`id`,`username`,`password`,`email`) VALUES(?,?,?,?);";
ps = conn.prepareStatement(sql);
ps.setInt(1,user.getId());
ps.setString(2, user.getUsername());
ps.setString(3, user.getPassword());
ps.setString(4, user.getEmail());
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.free(null,ps,conn);
}
}
//修改数据库用户记录的方法update
public void update(User user){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtils.getConnection();
String sql = "UPDATE t_user set username=?,password=?,email=? where id=?";
ps = conn.prepareStatement(sql);
ps.setString(1,user.getUsername());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
ps.setInt(4,user.getId());
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.free(null,ps,conn);
}
}
//删除数据库用户记录方法
public void delete(int id){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtils.getConnection();
String sql = "Delete From t_user where id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1,id);
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.free(null,ps,conn);
}
}
//根据id查询用户的方法findUserById()
public User findUserById(int id){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select * from t_user where id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1,id);
rs=ps.executeQuery();
if (rs.next()){
user = new User();
user.setId(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
user.setEmail(rs.getString(4));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.free(rs,ps,conn);
}
return user;
}
//查询所有用户记录，QueryAll()
public List QueryAll(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List userList = new ArrayList();
try{
conn = JdbcUtils.getConnection();
String sql = "SELECt * FROM t_user";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
User user=new User();
user.setId(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
user.setEmail(rs.getString(4));
userList.add(user);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.free(rs,ps,conn);
}
return userList;
}
}
问题记录：在jsp中编写代码调用里面的一些方法时一老出现空指针报错，不知道为什么
