使用JDBC
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
`id`INT PRIMARY KEY,
`name` VARCHAR (40),
`password` VARCHAR (40),
`email` VARCHAR(60),
`birthday` DATE
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)
VALUE (1,'zhangsan','123456','za@sina.com','1980-12-04'),
(2,'lisi','123456','li@sina.com','1981-12-04'),
(3,'wangwu','123456','wang@sina.com','1979-12-04');
第一个JDBC程序
import java.sql.*;
public class JdbcFirstDemo {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//固定写法,加载驱动
//2.用户信息和url
//useUnicode=ture&characterEncoding=utf8&useSSL=ture
String url = "jdbc:mysql://192.168.31.112:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username ="root";
String password ="123456";
//3.连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username,password);
//4.执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象去执行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();
}
}
步骤总结:
1.加载驱动
2.连接数据库
3.获取执行Sql 的
4.获得返回结果集
5.关闭资源
DriverManager
//1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver() );
Class.forName("com.mysql.jdbc.Driver");
//固定写法,加载驱动
//
//3.连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username,password);
connection.rollback();
connection.commit();
connection.getAutoCommit();
url
//2.用户信息和url
//useUnicode=ture&characterEncoding=utf8&useSSL=ture
String url = "jdbc:mysql://192.168.31.112:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql 3306
//jdbc:mysql
//oralce --1521
//jdbc:orcal:thin:@localhost:1521:sid
statement执行SQL对的对象。prepareStatement执行SQL的对象
statement.executeQuery(); //查询返回操作
statement.execute();//执行任务SQL
statement.executeUpdate(); //更新插入删除都用他
ResultSet结果集对象:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
绝对定位
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous(); //移动到前定行
resultSet.absolute(row);//移动指定行
** 代码实现(提取工具类)**
//文件名字:db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.31.112:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
import java.io.IOException;
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");
//驱动只用加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException 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 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();
String sql = "INSERT INTO `users` (`id` ,`name`,`password`,`email`,`birthday`)" +
"VALUES(6,'liuliu','123456','liu@sina.com','1977-12-04');";
int i = st.executeUpdate(sql);
// st.executeQuery() 查询执行
if (i > 0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
sql注入的问题
sql存在漏洞,导致被攻击。
SQL 会被拼接,导致攻击。
prepareStatement对象
本质是繁殖SQL注入的本质,把传递进来的参数当作字符
引号会被转义掉。
PreparedStatement对象可以防止sql注入。效率更好!
import com.duan.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
//1.连接
conn = JdbcUtils.getConnection();
//区别
//使用?占位符
String sql = "insert into users(id,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)";
//2.获取prepareStatement对象
pstm = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//3.手动给参数赋值
pstm.setInt(1, 4);//id
pstm.setString(2, "leiwei");
pstm.setString(3, "123456");
pstm.setString(4, "123456@qq.com");
// 注意点:sql.Date 数据库
// util.Date Java new Date().getTime() 获得时间戳
pstm.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
//4.执行:
int i = pstm.executeUpdate();
if (i > 0) {
System.out.println("插入成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.释放
JdbcUtils.release(conn, pstm, null);
}
}
}
结果:
插入成功!
2.删除:
import com.duan.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
//1.连接
conn = JdbcUtils.getConnection();
//区别
//使用?占位符
String sql = "delete from users where id = ? ";
//2.获取prepareStatement对象
pstm = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//3.手动给参数赋值
pstm.setInt(1,4);
//4.执行:
int i = pstm.executeUpdate();
if (i > 0) {
System.out.println("删除成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.释放
JdbcUtils.release(conn, pstm, null);
}
}
}
结果:
删除成功!
3.更新:
import com.duan.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
//1.连接
conn = JdbcUtils.getConnection();
//区别
//使用?占位符
String sql = "update users set `name`=? where id=?;";
//2.获取prepareStatement对象
pstm = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//3.手动给参数赋值
pstm.setString(1,"断浮");
pstm.setInt(2,1);
//4.执行:
int i = pstm.executeUpdate();
if (i > 0) {
System.out.println("更新成功!");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.释放
JdbcUtils.release(conn, pstm, null);
}
}
}
结果:
更新成功!
4.查询:
import com.duan.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取连接
String sql = " select * from users where id = ? ";//编写sql
pstm = conn.prepareStatement(sql);//预编译
pstm.setInt(1, 1);//传递参数
rs = pstm.executeQuery();//执行
if (rs.next()) {
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, pstm, rs);
}
}
}
防止sql注入
package com.duan.demo03;
import com.duan.demo02.utils.JdbcUtils;
import java.sql.*;
public class 防止SQL注入 {
public static void main(String[] args) {
//login("lisi","123456");
login("'' or 1=1 ", "123456");
}
public static void login(String username, String password) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//PreparedStatement,防止sql注入的本质:把传递进来的参数当做字符
//假设其中存在转义字符,比如说 ' 会被直接转义
String sql = "select * from users where `name`=? and `password`=?";
st = conn.prepareStatement(sql);
st.setString(1, username);
st.setString(2, password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("================================");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}