JDBC
前言:先在idea中导入驱动jar包
一、JDBC
JDBC访问数据库步骤
- 加载驱动
- 获取连接
- 创建Statement,执行Sql语句
- 返回查询结果
- 关闭资源
具体代码如下:
Statement
public static void main(String[] args) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接(url,username,password)
String url = "jdbc:mysql://192.168.XXX.XXX:3306/test";
Connection conn = DriverManager.getConnection(url,"root","root");
//3.执行sql语句
String sql = "select * from grade";
Statement state = conn.createStatement();
ResultSet rs = state.executeQuery(sql);
while (rs.next()){
int gradeId = rs.getInt(1);
String gradeName = rs.getString(2);
System.out.println(gradeId+"\t "+gradeName);
}
//4.关闭
rs.close();
state.close();
conn.close();
}
//desc
String sql = "desc student"; //SQL语句
ResultSet set = query(sql); //返回结果集
while (set.next()){
int i = 1;
while (i<=set.getMetaData().getColumnCount()){
System.out.print(set.getObject(i)+"\t");
i++;
}
System.out.println();
}
set.close();
PreparedStatement
package Test1;
import java.sql.*;
/**
* @Author shall潇
* @Date 2021/3/5
* @Description
*/
public class TestPreparedState {
private static final String URL = "jdbc:mysql://192.168.XXX.XXX:3306/test";
private static Connection conn;
private static PreparedStatement ps;
private static ResultSet rs;
public static void getConn(){ //获取连接
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(URL,"root","root");
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void query(String sql,String...params){ //查询
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) { //多个参数依次赋值
ps.setObject(i+1,params[i]);
}
rs = ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static boolean update(String sql,String...params){ //增删改
int num = -1;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
num = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return num>0;
}
public static void close(){
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != rs)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (null != ps) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void show() throws SQLException {
while (rs.next()){
int i = 1;
while (rs.getMetaData().getColumnCount() >= i) {
System.out.print(rs.getObject(i)+"\t");
i++;
}
System.out.println();
}
}
public static void main(String[] args) throws SQLException {
getConn();
System.out.println("原始数据");
String querysql = "select * from student";
query(querysql);
show();
String upsql1 = "update student set stuAddr =? where stuName=?";
update(upsql1,"江西","李安");
System.out.println("更新后");
query(querysql);
show();
String insrtsql = "insert into student(stuId,stuName,gradeId,stuGender)values(?,?,?,?)";
update(insrtsql,"10","宋慧乔","3","女");
String desql = "delete from student where stuName = ?";
update(desql,"宋慧乔");
System.out.println("删除后");
query(querysql);
show();
}
}
二、Statement和PreparedStatement区别
Statement由方法createStatement()创建,该对象用于发送简单的SQL语句
使用 Statement 对象。在对数据库只执行一次性存取的时侯,用 Statement 对象进行处理。PreparedStatement 对象的开销比Statement大,对于一次性操作并不会带来额外的好处。
PreparedStatement由方法prepareStatement()创建,该对象用于发送一个或多个输入参数的SQL语句
- SQL语句使用"?"作为数据占位符
- 使用setXXX() 方法设置数据
PreparedStatement比Statement具有更好的维护性和可读性
PreparedStatement—预编译
PreparedStatement可以阻止常见的SQL注入式攻击。【例如:or ‘1=1’】
PreparedStatement可以写动态查询语句
PreparedStatement可以写参数化查询,比Statement能获得更好的性能。
PreparedStatement,数据库可以使用已经编译过及定义好的执行计划,这种预处理语句查询比普通的查询运行速度更快。