JDBC
数据库驱动
驱动:声卡,显卡,数据库
应用程序无法直接连接数据库,需要先连接数据库驱动,通过数据库驱动连接数据库,对数据库操作。
JDBC
SUN公司为了简化开发人员(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC
这些规范的实现由具体的厂商去做
我们只需要掌握JDBC接口操作即可应用程序--->JDBC(开发人员)--->MySQL驱动(数据库厂商)--->数据库
java.sql
,javax.sql
,数据库驱动包mysql-connector-java-8.0.2.jar
第一个 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`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
public class jdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
//用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
String username = "root";
String password = "szy10086";
//连接成功,connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
// connection.rollback();
// connection.commit();
// connection.setAutoCommit(true);
//执行sql对象 statement 执行sql的对象
Statement statement = connection.createStatement();
//执行sql的对象 去 执行sql
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了全部查询出来的结果
// statement.executeQuery();//查询操作,返回ResultSet
// statement.execute();//执行任何SQL
// statement.executeUpdate();//更新、插入、删除,都是这个,返回一个受影响的行数
// statement.executeBatch();//执行多个sql
// resultSet.getObject();//在不知道列类型的情况下使用
// resultSet.getString();//如果知道列类型就使用指定的类型
// resultSet.getInt();
// resultSet.getFloat();
// resultSet.getDate();//....
// resultSet.beforeFirst();//移动到最前面
// resultSet.afterLast();//移到最后面
// resultSet.next();//移动到下一个数据
// resultSet.previous();//移动到前一个
// resultSet.absolute();//移动在指定行
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("birthday="+resultSet.getObject("birthday"));
System.out.println("=================");
}
//释放连接
resultSet.close();
statement.close();
connection.close();//耗资源
}
}
- 注册驱动
- 连接数据库 DriverManager
- 获得执行sql的对象Statement
- 获得返回的结果集
- 释放连接
statement对象详解
编写一个工具类
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("database.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) {
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 throwables) {
throwables.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
测试是否可以插入成功
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(4,'hudu','123456','3535345@qq.com','2020-07-25')";
int i = st.executeUpdate(sql);
if (i>0) {
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL 注入问题
SQL注入代码实例
public class loginTest {
public static void main(String[] args) {
// login("hudu","123456");
// login("hudu","123");
login(" ' or '1=1"," ' or '1=1");
}
public static void login(String username,String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SELECT * FROM users WHERE `NAME` = 'hudu' and password = '123456';
//SELECT * FROM users WHERE `NAME` = ' ' or '1=1' and password = ' ' or '1=1';
String sql = "select * from users where `name` = '"+username+"' and `password` = '"+password+"'";
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("=========================");
}
//select
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
主要是因为 or 字符拼接
PreparedStatement 对象
PreparedStatement可以防止 SQL 注入,效率更高。
这里以插入为例
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//使用问好占位符,代替参数
String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
//区别
st = conn.prepareStatement(sql);//预编译sql
//手动给参数赋值
st.setInt(1,5);//id
st.setString(2,"lucy");
st.setString(3,"123456");
st.setString(4,"2134125@qq.com");
// 注意点:sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0) {
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(conn,st,null);
}
}
}
PrepareStatement防止SQL注入的本质,把传递进来的参数当作字符,假设其中存在转义字符,就会被忽略。
本作品采用《CC 协议》,转载必须注明作者和本文链接