1、数据库配置文件jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=
2、数据库数据类JdbcUtils
package com.test; import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties; public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
// 加载驱动，获取数据库连接信息
static {
try {
// 加载配置文件
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("DB.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// 加载驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} /**
* 获取数据库连接
* @throws SQLException
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
} /**
* 释放资源
* @param connection
* @param preparedStatement
* @param resultSet
*/
public static void releaseDB(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3、增删改查
package com.test; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; public class JdbcTest {
public void insert() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 获取连接
connection = JdbcUtils.getConnection();
// 准备sql语句
String sql = "INSERT INTO user(id,name,age) VALUES(?,?,?)";
// 获取PrepareStatement对象
preparedStatement = connection.prepareStatement(sql);
// 填充占位符
preparedStatement.setInt(1, 6);
preparedStatement.setString(2, "小A");
preparedStatement.setInt(3, 25);
// 执行sql
int num = preparedStatement.executeUpdate();// 返回影响到的行数 System.out.println("一共影响到" + num + "行");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.releaseDB(connection, preparedStatement, null);
}
}
public void update() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 获取连接
connection = JdbcUtils.getConnection();
// 准备sql语句
String sql = "update user set age = ? where name = ?";
// 获取PrepareStatement对象
preparedStatement = connection.prepareStatement(sql);
// 填充占位符
preparedStatement.setInt(1, 30);
preparedStatement.setString(2, "小A");
// 执行sql
int num = preparedStatement.executeUpdate();// 返回影响到的行数 System.out.println("一共影响到" + num + "行");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.releaseDB(connection, preparedStatement, null);
}
}
public void query() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "SELECT name,age FROM user";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery(); // 遍历结果集
while (resultSet.next()) {
String name = resultSet.getString(1);
Integer age = resultSet.getInt(2); System.out.println("name:" + name + ", age:" + age);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.releaseDB(connection, preparedStatement, resultSet);
}
}
public void delete() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
String sql = "DELETE FROM user WHERE id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 6);
int num = preparedStatement.executeUpdate(); System.out.println("一共影响到" + num + "行");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.releaseDB(connection, preparedStatement, null);
}
} }