JDBC简介
JDBC定义
JDBC常用接口、类
DBC快速入门
下载JDBC驱动
创建项目,导入jar包
开始编码连接数据库
package com.offcn.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJdbc {
//添加一条记录到数据库中
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/day08";
String username = "root";
String password = "root";
//建立数据库连接
Connection connection = DriverManager.getConnection(url, username, password);
//创建操作数据对象
Statement statement = connection.createStatement();
//写sql代码
String sql = "insert into user(id,name,age,sex) values(null,'李四',20,'男')";
//执行操作
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("操作成功...");
}
else{
System.out.println("操作失败...");
}
}
}
JDBC代码详解
注册驱动
建立数据库连接
创建执行对象 Statement
编写并执行sql代码
遍历结果集(查询操作才有)
释放资源(成功失败都有释放)
完整代码
//查询
public static void main(String[] args) {
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql:///day08", "root", "root");
//创建操作对象 Statement
statement = conn.createStatement();
//编写并执行sql语句
resultSet = statement.executeQuery("select * from user");
//遍历解析结果集
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String sex = resultSet.getString("sex");
System.out.println(id+"\t"+name+"\t"+age+"\t"+sex);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//释放资源 有顺序要求 先开后关
try {
if(resultSet!=null)
resultSet.close();
if(statement!=null)
statement.close();
if(conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
Junit单元测试
代码示例
完整代码:
package com.offcn.test;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
public class TestJdbc {
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;
//操作数据库前的准备
@Before
public void init() throws ClassNotFoundException, SQLException {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql:///day08", "root", "root");
//创建操作对象 Statement
statement = conn.createStatement();
}
//操作数据库后的准备 释放资源
@After
public void destory(){
try {
if(resultSet!=null)
resultSet.close();
if(statement!=null)
statement.close();
if(conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void update(){
try {
//编写并执行sql语句
int id = 1;
String name = "张三";
String sex = "女";
int i = statement.executeUpdate("update user set name = '"+name+"', sex = '"+sex+"' where id = "+id);
System.out.println(i>0?"修改成功!!":"修改失败");
} catch (SQLException exception) {
exception.printStackTrace();
}
}
@Test
public void del(){
try {
int id = 3;
int i = statement.executeUpdate("delete from user where id =" + id);
System.out.println(i>0?"删除成功!!":"删除失败");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void query(){
try {
//编写并执行sql语句
resultSet = statement.executeQuery("select * from user");
//遍历解析结果集
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String sex = resultSet.getString("sex");
System.out.println(id+"\t"+name+"\t"+age+"\t"+sex);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
PreparedStatement
登录案例(Statement,会引起sql注入)
登录案例(PreparedStatement,预编译对象,不会引起sql注入)
PreparedStatement的CRUD
完整代码:
package com.offcn.test;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
import java.util.Scanner;
public class TestJdbcPreparedStatement {
Connection conn = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
//操作数据库前的准备
@Before
public void init() throws ClassNotFoundException, SQLException {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
conn = DriverManager.getConnection("jdbc:mysql:///day08", "root", "root");
}
//操作数据库后的准备 释放资源
@After
public void destory(){
try {
if(resultSet!=null)
resultSet.close();
if(statement!=null)
statement.close();
if(conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void update(){
try {
//编写并执行sql语句
int id = 1;
String name = "张三";
String sex = "女";
String sql = "update user set name = ?, sex = ? where id = ?";
statement = conn.prepareStatement(sql);
statement.setString(1,name);
statement.setString(2,sex);
statement.setInt(3,id);
int i = statement.executeUpdate();
System.out.println(i>0?"修改成功!!":"修改失败");
} catch (SQLException exception) {
exception.printStackTrace();
}
}
@Test
public void del(){
try {
int id = 3;
statement = conn.prepareStatement("delete from user where id = ?");
statement.setInt(1,id);
int i = statement.executeUpdate();
System.out.println(i>0?"删除成功!!":"删除失败");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void query(){
try {
//编写并执行sql语句
statement = conn.prepareStatement("select * from user");
//如果没有参数就不需要 传参
resultSet = statement.executeQuery();
//遍历解析结果集
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String sex = resultSet.getString("sex");
System.out.println(id+"\t"+name+"\t"+age+"\t"+sex);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//添加一条记录到数据库中
@Test
public void add() throws ClassNotFoundException, SQLException {
String sql = "insert into user(id,name,age,sex) values(null,?,?,?)";
//创建操作数据对象
statement = conn.prepareStatement(sql);
statement.setString(1,"张三");
statement.setInt(2,12);
statement.setString(3,"男");
//执行操作
int i = statement.executeUpdate();
if(i>0){
System.out.println("操作成功...");
}
else{
System.out.println("操作失败...");
}
}
@Test
public void login(){
Scanner input = new Scanner(System.in);
try{
System.out.println("请输入账号");
String name = input.nextLine();
System.out.println("请输入密码");
String password = input.nextLine();
// ' or 1=1 #
String sql = "select * from user where name = ? and password = ?";
//创建操作对象 Statement
statement = conn.prepareStatement(sql);
statement.setString(1,name);
statement.setString(2,password);
ResultSet resultSet = statement.executeQuery();
if(resultSet.next())
System.out.println("登录成功");
else
System.out.println("登录失败");
}catch(Exception e){
e.printStackTrace();
}
}
}
编写jdbc连接工具类
代码
工具类通用增删改(了解)