JDBC
数据库驱动和JDBC
(1)数据库驱动是连接数据库所需，由厂商提供，程序和数据库驱动打交道
(2)jdbc是一种规范，主要是为了简化开发人员对数据的统一的操作而提供的一个规范
第一个JDBC程序
package com.ch.jdbc;
import java.sql.*;
public class MyFirstJdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//2、准备连接信息：url，username，password
String url = "jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "root";
//3、连接数据库
Connection conn = DriverManager.getConnection(url, username, password);
//4、获取执行SQL语句的对象
Statement statement = conn.createStatement();
//5、执行SQL语句
ResultSet query = statement.executeQuery("select * from student");
while (query.next()){
int id = query.getInt("id");
int score = query.getInt("score");
String name = query.getString("name");
System.out.println(id + "------"+ name + "------"+score);
}
//6、释放资源
query.close();
statement.close();
conn.close();
}
}
URL
String url = "jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true";
//mysql -- 3306
//jdbc:mysql://localhost:3306/数据库?参数1&参数2&参数3
//oracle -- 1521
//jdbc:oracle:thin@localhost:1521:sid
//SQLServer -- 1433
//jdbc:sqlserver://localhost:1433;DatabaseName=test
DriverManager
Connection conn = DriverManager.getConnection(url, username, password);
//conn代表数据库对象
//数据库可以设置提交
//设置回滚
//设置自动提交
conn.commit();
conn.rollback();
conn.setAutoCommit();
Statement
Statement statement = conn.createStatement();
//statement用来执行SQL的对象，prepareStatement也是用来执行SQL对象的
statement.execute(); //执行任何SQL
statement.executeQuery(); //查询操作，返回ResultSet
statement.executeUpdate(); //更新、插入、删除。返回受影响行
statement.executeBatch(); //执行多个SQL
ResultSet
//封装了所有的查询结果
query.getObject(); //不知道数据类型的情况下使用
query.getString();
query.getInt();
query.getDate();
……
简单封装代码实现
jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true
username=root
password=root
JdbcUtil
package com.ch.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
//1、获取配置文件的信息并加载驱动
static {
try {
//读取配置文件的内容
InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.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 (Exception e) {
}
}
//2、获取连接
public static Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//3、释放资源
public static void close(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();
}
}
}
}
JdbcTest
package com.ch.jdbc;
import com.ch.util.JdbcUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 增删查改测试
*/
public class JdbcTest {
/**
* 增删改操作
* @param sql 填写需要执行的sql语句
* @param message 操作是增加还是删除或者修改
*/
public static void update(String sql,String message){
//获取连接
Connection conn = JdbcUtil.getConnection();
//获取执行SQL的对象
Statement st = null;
int result = 0;
try {
st = conn.createStatement();
//执行SQL
result = st.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
if (result > 0){
System.out.println(message+"成功");
}else{
System.out.println(message+"失败");
}
//执行完释放资源
JdbcUtil.close(conn,st,null);
}
/**
* 查询操作
*/
public static void query(){
//获取连接
Connection conn = JdbcUtil.getConnection();
//获取执行SQL的对象
Statement st = null;
ResultSet rs = null;
try {
st = conn.createStatement();
//执行SQL语句
String sql = "select * from student";
rs = st.executeQuery(sql);
while (rs.next()){
int id = rs.getInt("id");
int score = rs.getInt("score");
String name = rs.getString("name");
System.out.println(id + "------"+ name + "------"+score);
}
} catch (SQLException e) {
e.printStackTrace();
}
//执行完释放资源
JdbcUtil.close(conn,st,null);
}
//测试
public static void main(String[] args) throws SQLException {
//添加
String sql = "insert into student(id,score,`name`) values(10,72,'小四');";
String message = "插入";
update(sql,message);
//修改
String sql1 = "update student set `name`='张华' where id = 10";
String message1 = "修改";
update(sql1,message1);
//删除
String sql2 = "delete from student where id = 10";
String message2 = "删除";
update(sql2,message2);
//查询
query();
}
}
SQL注入问题
SQL注入就是sql存在漏洞，SQL语句被拼接
public static void doLogin(String uname){
//获取连接
Connection conn = JdbcUtil.getConnection();
//获取执行SQL的对象
Statement st = null;
ResultSet rs = null;
try {
st = conn.createStatement();
//执行SQL语句
String sql = "select * from student where `name`='"+uname+"'";
rs = st.executeQuery(sql);
while (rs.next()){
int id = rs.getInt("id");
int score = rs.getInt("score");
String name = rs.getString("name");
System.out.println(id + "------"+ name + "------"+score);
}
} catch (SQLException e) {
e.printStackTrace();
}
//执行完释放资源
JdbcUtil.close(conn,st,null);
}
防SQL注入改进
使用PrepareStatement 可以防止SQL注入，效率更高(预编译)
package com.ch.jdbc;
import com.ch.util.JdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 防SQL注入问题的一种改进
*/
public class PreJdbcTest {
public static void main(String[] args) {
Connection conn = JdbcUtil.getConnection();
String sql = "select * from student where `name`= ?";
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst = conn.prepareStatement(sql);
pst.setString(1,"张三");
rs = pst.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
int score = rs.getInt("score");
String name = rs.getString("name");
System.out.println(id + "------"+ name + "------"+score);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}