使用Jdbc连接数据库
java">//1:加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2:用户信息和url
String url="jdbc:mysql://localhost:3306/jpa?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=true";
//对于mysql8版本,serverTimezone是必须要设置的,否则会报异常
String username="root"; //数据库连接名
String password="123456"; //数据库密码
// 3:连接成功,数据库对象,connection代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
// 4:执行sql对象statement
Statement statement=connection.createStatement();
// 5:执行sql语句,并返回结果
String sql="select * from tbl_user";
ResultSet resultSet=statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id"+resultSet.getObject("id"));
System.out.println("id"+resultSet.getObject("email"));
System.out.println("id"+resultSet.getObject("name"));
}
// 6:关闭所有连接,避免浪费资源
resultSet.close();
statement.close();
connection.close();
不过以上代码耦合性太高,可以写一个配置类,在配置类中放置静态代码,解耦!
首先在src目录下创建一个db.properties配置文件用来放置数据库连接信息
java">driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jpa?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=true
username=root
password=123456
//注意:这里是的对象都不需要引号,否则会报SQL异常(找不到连接名)
开始写工具类:
一)首先定义好我们需要用来的变量(包括连接对象,账号密码,返回结果集等):
java"> private static ResultSet resultset; //定义数据库返回对象
private static Connection connection; //定义数据库连接对象
private static Statement statement;
private static String driver; //定义连接名
private static String url; //定义连接地址
private static String username; //定义用户名
private static String password; //定义密码
二)利用文件流操作读取配置文件中的信息,并且加载连接数据库驱动
java">static{
try{
//读取配置文件
InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(resourceAsStream); //加载配置文件
driver = properties.getProperty("driver"); //从配置文件中读取值
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);//加载类路径
} catch (Exception e) {
e.printStackTrace();
}
三)最后再写两个方法用来返回连接对象和释放资源
java">/**
* 返回一个connection对象
* @return
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
/**
* 释放资源
*/
public void ReleaseResource(){
try{
if(resultset != null){
resultset.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
最后整合一下就是:
java">
public class JdbcUtils {
private static ResultSet resultset; //定义数据库返回对象
private static Connection connection; //定义数据库连接对象
private static Statement statement;
private static String driver; //定义连接名
private static String url; //定义连接地址
private static String username; //定义用户名
private static String password; //定义密码
static{
try{
//读取配置文件
InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(resourceAsStream); //加载配置文件
driver = properties.getProperty("driver"); //从配置文件中读取值
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);//加载类路径
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 返回一个connection对象
* @return
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
/**
* 释放资源
*/
public void ReleaseResource(){
try{
if(resultset != null){
resultset.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
java">public class TestSelect {
public static Connection connection;
public static Statement statement;
public static void main(String[] args) throws SQLException {
TestSelect.Test1();
}
public static void Test1() throws SQLException {
String sql="select * from tbl_user";
JdbcUtils jdbcUtils = new JdbcUtils();
connection = jdbcUtils.getConnection();
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println(resultSet.getString("name"));
}
jdbcUtils.ReleaseResource();
}
}
增删改查的代码基本都是一样的,需要改动的地方也就是
java">ResultSet resultSet = statement.executeQuery(sql);
查询的话就使用executeQuery方法;
删改查就使用executeUpdate方法