JDBC
JDBC Java Database connectivity
Java数据库连接规范(一套接口) 由SUn公司提供的
JDBC四个核心类
DriverManager 创建连接
Connection 连接类
Statement 执行sql语句
ResultSet 结果集
JDBC连接步骤:
1.注册驱动
2.获取连接 Connection
3.获取sql 语句的执行对象 Statement
4.执行sql 语句 返回结果集
5.处理结果集
6.关闭资源
JDBC 连接
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/myjdbc";
String url2 = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
Connection connection = DriverManager.getConnection(url2);
Statement statement = connection.createStatement();
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getObject(1));
System.out.println(resultSet.getObject(2));
System.out.println(resultSet.getObject(3));
System.out.println(resultSet.getObject(4));
System.out.println(resultSet.getObject(5));
System.out.println("--------------------");
}
resultSet.close();
statement.close();
connection.close();
}
测试增删改查
// 注解 用来测试方法
// 注意: 要使用public修饰 无返回值方法
// 插入方法
@Test
public void testInsert() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
String sql = "insert into users values(5,'f','123456','w@qq.com','1997-06-01')"
int row = statement.executeUpdate(sql)
if (row > 0) {
System.out.println("插入成功")
}
statement.close()
connection.close()
}
// 修改方法
@Test
public void testUpdate() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
String sql = "update users set name = 'g' where name='f'"
// 受影响的行数
int update = statement.executeUpdate(sql)
if (update > 0) {
System.out.println("更新成功" + update + "行")
}
statement.close()
connection.close()
}
// 删除方法
@Test
public void testDelete() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
String sql = "delete from users where id=5 "
int row = statement.executeUpdate(sql)
if (row > 0) {
System.out.println("删除成功")
}
statement.close()
connection.close()
}
// 查询方法
//@Test写入时需要选择导入 import org.junit.Test
@Test
public void testSelect() throws Exception {
Class.forName("com.mysql.jdbc.Driver")
String url = "jdbc:mysql://localhost:3306/myjdbc"
Connection connection = DriverManager.getConnection(url, "root", "123456")
Statement statement = connection.createStatement()
// 查询
String sql = "select id,name,email from users"
ResultSet resultSet = statement.executeQuery(sql)
// 处理结果集
while (resultSet.next()) {
// 可以直接填字段名称
System.out.println(resultSet.getObject("id"))
System.out.println(resultSet.getObject("name"))
System.out.println(resultSet.getObject("email"))
}
resultSet.close()
statement.close()
connection.close()
}
连接数据库的异常处理
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/myjdbc";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
ArrayList<User> arrayList = new ArrayList<>();
while (resultSet.next()) {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEMail(resultSet.getString("email"));
user.setBirthday(resultSet.getDate("birthday"));
arrayList.add(user);
}
for (User user : arrayList) {
System.out.println(user);
}
} catch (ClassNotFoundException e) {
throw new RuntimeException("驱动加载失败");
} catch (SQLException e) {
throw new RuntimeException("获取连接失败");
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
connection = null;
}
}
JDBC工具类
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:
user=root
password=123456
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
driverClass = rb.getString("driverClass");
url = rb.getString("url");
user = rb.getString("user");
password = rb.getString("password");
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException, ClassNotFoundException {
return DriverManager.getConnection(url, user, password);
}
public static void closeALL(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("关闭失败");
}
connection = null;
}
}
@Test
public void testSelect() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
ArrayList<User> arrayList = new ArrayList<>();
while (resultSet.next()) {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
user.setEMail(resultSet.getString("email"));
user.setBirthday(resultSet.getDate("birthday"));
arrayList.add(user);
}
for (User user : arrayList) {
System.out.println(user);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.closeALL(resultSet, statement, connection);
}
}