JDBC 连接操作
下面案例都是以MySQL数据库进行的
package com.ysh;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @className: com.ysh.JDBCDemo1
* @description: TODO
* @author: YSH
* @create: 2021-08-30 18:58
*/
public class JDBCDemo1 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
//1.注册驱动
//第一种写法,一般都用第二种写法通过反射加载
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String pwd = "ysh";
conn = DriverManager.getConnection(url, user, pwd);
//3.获取数据库操作对象
stmt = conn.createStatement();
//4.执行SQl语句
String sql = "insert into t_user(no,name,pwd) values (5,'iah','123')";
//5.获取结果集
int count = stmt.executeUpdate(sql);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
新建一个properties文件,讲数据库内容配置到文件中 如果在maven项目下就放到resources下面
package com.ysh;
import java.sql.*;
import java.util.ResourceBundle;
/**
* @className: com.ysh.JDBCDemo1
* @description: TODO
* @author: YSH
* @create: 2021-08-30 18:58
*/
public class JDBCDemo2 {
public static void main(String[] args) {
//使用资源绑定器绑定属性配置文件
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String pwd = bundle.getString("pwd");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1.注册驱动
//第一种写法,一般都用第二种写法通过反射加载
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName(driver);
//2.获取连接
conn = DriverManager.getConnection(url, user, pwd);
//3.获取数据库操作对象
stmt = conn.createStatement();
//4.执行SQl语句
String sql = "select * from t_user ";
rs = stmt.executeQuery(sql);
//5.获取结果集
while (rs.next()) {
String no = rs.getString("no");
String name = rs.getString("name");
String password = rs.getString("pwd");
System.out.println(no + "-" + name + "-" + password);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
sql注入 fdse’ or ‘1’='1
解决方法将Statement换成PreparedStatement
下面是代码案例以及事务的提交
package com.ysh;
import java.sql.*;
import java.util.ResourceBundle;
/**
* @className: com.ysh.JDBCDemo1
* @description: TODO
* @author: YSH
* @create: 2021-08-30 18:58
*/
public class JDBCDemo3 {
public static void main(String[] args) throws SQLException {
//使用资源绑定器绑定属性配置文件
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String pwd = bundle.getString("pwd");
Connection conn = null;
//将提交改为手动提交 开启事务
conn.setAutoCommit(false);
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.注册驱动
//第一种写法,一般都用第二种写法通过反射加载
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName(driver);
//2.获取连接
conn = DriverManager.getConnection(url, user, pwd);
//3.获取数据库操作对象
String sql = "select * from t_user where no=? and name=? ";
ps = conn.prepareStatement(sql);//对sql语句进行预编译
//jdbc下标从1开始,第一个参数表示第几个问号的,第二个参数表示传递的值
ps.setString(1, "1");
ps.setString(2, "ysh");
//4.执行SQl语句
rs = ps.executeQuery();//里面不用放sql语句
//5.获取结果集
while (rs.next()) {
String no = rs.getString("no");
String name = rs.getString("name");
String password = rs.getString("pwd");
System.out.println(no + "-" + name + "-" + password);
}
//手动提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
conn.rollback(); //回滚事务
} finally {
//6.释放资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
封装DBUtil工具类
package com.ysh;
import java.sql.*;
import java.util.ResourceBundle;
/**
* @className: com.ysh.DBUtil
* @description: TODO
* @author: YSH
* @create: 2021-08-30 20:16
*/
public class DBUtil {
private static final ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
private static final String driver = bundle.getString("driver");
private static final String url = bundle.getString("url");
private static final String user = bundle.getString("user");
private static final String pwd = bundle.getString("pwd");
/*
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String pwd = "ysh";
jdbc.properties文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
user=root
pwd=ysh
* */
private DBUtil() {
}
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/*
* 获取数据库连接对象
* */
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, pwd);
}
/*
* 关闭资源
* */
public static void close(Connection conn, Statement ps, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
使用DBUtil
package com.ysh;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @className: com.ysh.JDBCDemo4
* @description: TODO
* @author: YSH
* @create: 2021-08-30 20:26
*/
public class JDBCDemo4 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from t_user where no=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 1);
rs = ps.executeQuery();
while (rs.next()) {
String no = rs.getString("no");
String name = rs.getString("name");
String password = rs.getString("pwd");
System.out.println(no + "-" + name + "-" + password);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, ps, rs);
}
}
}