import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; import org.apache.commons.dbcp.BasicDataSource; public class DBUtils {
private static BasicDataSource dateSource;
static {
//创建属性对象
Properties prop = new Properties();
//得到文件的输入流
InputStream ips = DBUtils2.class.getClassLoader().getResourceAsStream("jdbc.properties");
//把文件加载到属性对象中
try {
prop.load(ips);
//读取数据
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String username = prop.getProperty("username");
String password = prop.getProperty("password");
//创建数据源对象
dateSource = new BasicDataSource();
//设置数据库链接信息
dateSource.setDriverClassName(driver);
dateSource.setUrl(url);
dateSource.setUsername(username);
dateSource.setPassword(password);
//设置连接池参数
dateSource.setInitialSize(3);//初始连接数量
dateSource.setMaxActive(5);//最大连接数量 } catch (IOException e) {
e.printStackTrace();
} }
//1、获取链接
public static Connection getConn() throws Exception { //获取连接池中的连接
Connection conn = dateSource.getConnection();
return conn;
}
//2、关闭资源
public static void close(ResultSet rs,Statement stat,Connection conn) {
try {
if (rs!=null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (stat!=null) {
stat.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
//关闭连接
try {
if (conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
DAO
/**
* 依据用户名查询对应的用户信息。 如果找不到，返回null。
*
* @throws SQLException
*/
public User find(String uname) throws SQLException {
User user = null; Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null; try {
conn = DBUtils.getconn();
String sql = "SELECT * FROM t_user " + "WHERE username=?";
ps = conn.prepareStatement(sql);
ps.setString(1, uname);
rs = ps.executeQuery(); if (rs.next()) {
int id = rs.getInt("id");
String pwd = rs.getString("password");
String email = rs.getString("email"); user = new User();
user.setId(id);
user.setUname(uname);
user.setPwd(pwd);
user.setEmail(email); } } catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
DBUtils.close(rs, ps, conn);
} return user;
}
/**
* 删除指定信息
* @param id
* @throws SQLException
*/
public void delete(int id) throws SQLException {
Connection conn = null;
PreparedStatement ps = null; try {
conn = DBUtils.getconn();
String sql = "DELETE FROM t_user " + "WHERE id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ps.executeUpdate(); } catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
DBUtils.close(null, ps, conn);
}
} /**
* 将用户信息插入到t_user表。
*
* @throws SQLException
*
*/
public void save(User user) throws SQLException {
Connection conn = null;
PreparedStatement ps = null; try {
conn = DBUtils.getconn();
String sql = "INSERT INTO t_user " + "VALUES(null,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, user.getUname());
ps.setString(2, user.getPwd());
ps.setString(3, user.getEmail());
ps.executeUpdate(); } catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
DBUtils.close(null, ps, conn);
}
} /**
* 从t_user表中查询出所有用户的信息。 注： 一条记录对应一个User对象(即将记录中的数据 存放到User对象里面)。
*
* @throws SQLException
*/
public List<User> findAll() throws SQLException { List<User> users = new ArrayList<User>(); Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null; try {
conn = DBUtils.getconn();
String sql = "SELECT * FROM t_user";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery(); while (rs.next()) { int id = rs.getInt("id");
String uname = rs.getString("username");
String pwd = rs.getString("password");
String email = rs.getString("email"); User user = new User();
user.setId(id);
user.setUname(uname);
user.setPwd(pwd);
user.setEmail(email); users.add(user); } } catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
DBUtils.close(rs, ps, conn);
} return users; }
Java中访问数据库的步骤
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//建立连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "");
System.out.println("创建完毕");
//创建Statement
Statement stat = conn.createStatement();
String sql = "delete from jdbc01 where id=1";
//执行sql语句(若SQL语句为查询语句需要处理结果集)
stat.executeUpdate(sql);
System.out.println("删除完毕");
//关闭连接
stat.close();
conn.close();
数据库的基本连接
public static void main(String[] args) throws Exception {
//创建数据源对象
BasicDataSource dateSource = new BasicDataSource();
//设置数据库连接信息
dateSource.setDriverClassName("com.mysql.jdbc.Driver");
dateSource.setUrl("jdbc:mysql://localhost:3306/db3");
dateSource.setUsername("root");
dateSource.setPassword("root");
//设置连接池参数
dateSource.setInitialSize(3);//初始连接数量
dateSource.setMaxActive(5);//最大连接数量
//获取连接池中的连接
Connection conn = dateSource.getConnection();
System.out.println(conn);
}