关键步骤
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//2.得到连接
ct = DriverManager.getConnection("jdbc:mysql://localhost:3306/usermanager","root","root");
//3.创建prepareStatement
ps = ct.prepareStatement("select * from users");
//4.执行操作
rs = ps.executeQuery();
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 操作数据库的工具类
*
* @author dell
*
*/
public class SqlHelper {
// 定义需要的变量
private static Connection ct = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
// 连接数据库的参数
private static String driver = "";
private static String url = "";
private static String username = "";
private static String password = "";
// 读取配置文件
private static Properties pp = null;
private static InputStream fis = null;
// 加载驱动 只需要一次
static {
try {
// 从dbinfo.properties文件中读取配置信息
pp = new Properties();
// 当我们使用java web的时候,读取文件要使用类加载器
// 【因为类加载器读取文件时 默认主目录为src目录】
fis = SqlHelper.class.getClassLoader().getResourceAsStream(
"dbinfo.properties");
pp.load(fis);
driver = pp.getProperty("driver");
url = pp.getProperty("url");
username = pp.getProperty("username");
password = pp.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (Exception e) {
e.printStackTrace();
}
fis = null;
}
}
// 得到连接
public static Connection getConnection() {
try {
ct = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return ct;
}
// 查询函数
public static ResultSet executeQuery(String sql, String[] paras) {
try {
ct = getConnection();
// 1.创建一个ps
ps = ct.prepareStatement(sql);
// 给sql语句中的?赋值
if (paras != null) {
for (int i = 0; i < paras.length; i++) {
ps.setString(i + 1, paras[i]);
}
}
// 执行
rs = ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static void executeUpdate(String sql, String[] paraments) {
try {
ct = getConnection();
ps = ct.prepareStatement(sql);
// 给?赋值
if (paraments != null) {
for (int i = 0; i < paraments.length; i++) {
ps.setString(i + 1, paraments[i]);
}
}
// 执行
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
// 抛出异常,抛出运行时异常,可以给调用函数的函数一个选择 可以处理,也可以放弃处理
throw new RuntimeException(e.getMessage());
} finally {
// 关闭资源
close(ct, ps, rs);
}
}
public static Connection getMyConnection() {
return ct;
}
public static PreparedStatement getMyPreparedStatement() {
return ps;
}
public static ResultSet getMyResultSet() {
return rs;
}
// 关闭资源
public static void close(Connection ct, PreparedStatement ps, ResultSet rs) {
if (ct != null) {
try {
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}