JDBC连接MySQL数据库
步骤:
- 加载驱动
- 连接数据库(DriverManager)
- 获得执行SQL的对象(statement)
- 获得返回的结果集(resultset)
- 释放连接
具体代码:
import java.sql.*;
//我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");// 固定写法,加载驱动
//2.用户信息和URL useUnicode=true 支持中文编码 characterEncoding=utf8 设置中文集为utf-8 &useSSL=true 使用安全链接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
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 `users`";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("pwd="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birth="+resultSet.getObject("birthday"));
}
//6.关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
statement对象
JDBC中的stetement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement.executeUpdate方法,用于向数据库发送增删改的SQL语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
增删改查操作--create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功")
}
增删改查操作--delete
使用exeucteUpdate(String sql)方法完成数据删除操作,示例操作:
Statement st = conn.createStatement();
String sql = "delete from user where id = 1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}
增删改查操作--update
使用exeucteUpdate(String sql)方法完成数据修改操作,示例操作:
Statement st = conn.createStatement();
String sql = "update user set name=‘‘ where name = ‘‘";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}
增删改查操作--query
使用exeucteQuery(String sql)方法完成数据查询操作,示例操作:
Statement st = conn.createStatement();
String sql = "select * from user where id = 1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型,分别调用rs的响应方法映射到java对象中
System.out.println("id="+resultSet.getInt("id"));
System.out.println("name="+resultSet.getString("NAME"));
System.out.println("pwd="+resultSet.getString("PASSWORD"));
System.out.println("email="+resultSet.geString("email"));
System.out.println("birth="+resultSet.getDate("birthday"));
}
提取工具类方法:
工具类代码:
import java.io.InputStream;
import java.sql.*;
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");
//1.驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
增加测试类:
import com.qsy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(4,‘zhaoliu‘,‘123456‘,‘444444@163.com‘,‘2021-5-27‘)";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
删除测试类:
import com.qsy.lesson02.utils.JdbcUtils;s
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "DELETE FROM users WHERE id = 4";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
修改测试类:
import com.qsy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "UPDATE users SET `NAME`=‘WANGWU‘ WHERE id = 3";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
查询测试类:
import com.qsy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestQuery {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "SELECT * FROM users WHERE id = 1";
rs = st.executeQuery(sql);//返回的结果集
while(rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("NAME"));
System.out.println("pwd="+rs.getObject("PASSWORD"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birth="+rs.getObject("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}