JDBC(一)

JDBC(Java DataBase Connective)---Java连接数据库

1)概念：使用Java代码操作数据库,JDBC其实就是一套操作数据库的规范(接口).

2)开发步骤:

(1)JDBC之DML
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class JDBCDML {
public static void main(String[] args) throws Exception {
// 1.导入驱动jar包(jar包 右键add--->builderpath)

// 2.加载驱动jar包
Class.forName("com.mysql.jdbc.Driver");
// 3.获取数据库连接对象
String url = "jdbc:mysql://localhost:3306/mydb_02";// "主协议:子协议://ip地址:端口号/数据库名称"
String user = "root";
String password = "root";
Connection conn =DriverManager.getConnection(url, user, password);
// 4.定义sql语句
String sql = "insert into student values(11,'qwq')";
// 5.获取操作对象
Statement stat = conn.createStatement();
// 6.执行sql语句
int i = stat.executeUpdate(sql);
if (i != 0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
// 7.释放资源
stat.close();
conn.close();
}
}

(2)JDBC之DQL(未封装,直接输出)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class JDBCDQL {
public static void main(String[] args) throws Exception {
// 1.导入驱动jar包
// 2.加载驱动jar包
Class.forName("com.mysql.jdbc.Driver");
// 3.获取数据库连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb_02", "root", "root");
// 4.定义sql语句
String sql = "select * from student";
// 5.获取操作对象
Statement statement = connection.createStatement();
// 6.执行sql语句
// ResultSet 结果集对象,用来封装查询出来的结果
ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println(id + "----" + name);
}


// 7.释放资源
resultSet.close();
statement.close();
connection.close();
}
}

(3)JDBC之DQL(使用对象封装)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import org.westos.bean.Student;

public class JDBCDQL2 {
public static void main(String[] args) throws Exception {
// 1.导入驱动jar包
// 2.加载驱动jar包
Class.forName("com.mysql.jdbc.Driver");
// 3.获取数据库连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb_02", "root", "root");
// 4.定义sql语句
String sql = "select * from student";
// 5.获取操作对象
Statement statement = connection.createStatement();
// 6.执行sql语句
// ResultSet 结果集对象,用来封装查询出来的结果
ResultSet resultSet = statement.executeQuery(sql);

Student student = null;
//创建Arraylist集合,用来存储Student对象
ArrayList<Student> list = new ArrayList<Student>();

while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");

student = new Student(id,name);

list.add(student);
}
System.out.println(list);

// 7.释放资源
resultSet.close();
statement.close();
connection.close();
}
}

import java.io.Serializable;
public class Student implements Serializable {
/**
* JavaBean 是一种规范要求： 1.所有成员变量必须私有 2.必须提供无参构造 3.必须提供get set 方法
* 4.最好实现Serializable 接口 JavaBean 作用 来封装数据
*/
private static final long serialVersionUID = 1L;
private int id;
private String name;

public Student() {

}

public Student(int id, String name) {
super();
this.id = id;
this.name = name;
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + "]";
}
}

(4)JDBC之DML(使用了工具类)
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.westos.jdbc.utils.JDBCUtil;

public class JDBCTest {
public static void main(String[] args) throws SQLException {
// 获取数据库连接对象
Connection conn = JDBCUtil.getConnection();

String sql = "insert into student values(12,?)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, "ewe");

int i = statement.executeUpdate();
if (i != 0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}

// 释放资源
JDBCUtil.close(conn, statement);
}
}

// JDBC工具类
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
private static String url = null;
private static String user = null;
private static String password = null;

static {
try {
// url = "jdbc:mysql://localhost:3306/mydb_02";
// user = "root";
// password = "root";
// Class.forName("com.mysql.jdbc.Driver");

//创建文件jdbc.properties

Properties properties = new Properties();
properties.load(new FileInputStream("src/jdbc.properties"));
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(properties.getProperty("driverpath"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

private JDBCUtil() {

}

// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}

// 释放资源
public static void close(Connection conn, Statement statement, ResultSet result) throws SQLException {
if (conn != null) {
conn.close();
}
if (statement != null) {
statement.close();
}
if (result != null) {
result.close();
}
}

// 释放资源
public static void close(Connection conn, Statement statement) throws SQLException {
if (conn != null) {
conn.close();
}
if (statement != null) {
statement.close();
}
}
}

3)预编译sql处理(防止sql注入)

(1) JDBC简易登陆之拼接字符串
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class JDBCLogin {
public static void main(String[] args) throws Exception {
// 创建键盘录入对象
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.next();
System.out.println("请输入用户密码:");
String password = sc.next();

// //sql注入
// String username="1' or '1'='1";
// String password="1' or '1'='1";

// 1.导入驱动jar包
// 2.加载驱动jar包
Class.forName("com.mysql.jdbc.Driver");
// 3.获取数据库连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb_02", "root", "root");

// 4.定义sql语句
String sql = "select * from login where username='" + username + "' and password='" + password + "'";

// 5.获取操作对象
Statement statement = connection.createStatement();
// 6.执行sql语句
ResultSet resultSet = statement.executeQuery(sql);

if (resultSet.next()) {
System.out.println("登陆成功");
} else {
System.out.println("登录失败");
}
}
}

(2)JDBC简易登陆之使用预编译防止sql注入
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class JDBCLogin2 {
public static void main(String[] args) throws Exception {
// 创建键盘录入对象
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.next();
System.out.println("请输入用户密码:");
String password = sc.next();


// //sql注入
// String username="1' or '1'='1";
// String password="1' or '1'='1";


// 1.导入驱动jar包
// 2.加载驱动jar包
Class.forName("com.mysql.jdbc.Driver");
// 3.获取数据库连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb_02", "root", "root");

// 4.定义sql语句
String sql = "select * from login where username=? and password=?";// 问号充当占位符

// 5.获取预编译操作对象
PreparedStatement statement = connection.prepareStatement(sql);
// 给问号赋值
statement.setString(1, username);
statement.setString(2, password);
// 6.执行sql语句
ResultSet resultSet = statement.executeQuery();

if (resultSet.next()) {
System.out.println("登陆成功");
} else {
System.out.println("登录失败");
}
}
}

4) 管理事务：事务指一件事，这件事一般包含多个步骤，这些步骤要么同时成功，要么同时失败。

//事务之转账
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.westos.jdbc.utils.JDBCUtil;

public class JDBCswTest {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement statement1 = null;
PreparedStatement statement2 = null;
try {
String sql1 = "update bank set money=money-1000 where name='老明'";
String sql2 = "update bank set money=money+1000 where name='小明'";

conn = JDBCUtil.getConnection();

conn.setAutoCommit(false);// 开启事务

statement1 = conn.prepareStatement(sql1);
statement2 = conn.prepareStatement(sql2);

//执行
statement1.executeUpdate();
System.out.println(1 / 0);
statement2.executeUpdate();


} catch (Exception e) {
try {
conn.rollback();// 回滚
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} finally {

try {
conn.commit();// 提交事务
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// 释放资源
try {
JDBCUtil.close(conn, statement1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
JDBCUtil.close(null, statement2);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}