JDBC:是SUN公司制定的一套接口,目的就是为了解耦合
每一个数据库都有自己的实现原理,Oracle有自己的实现原理,Mysql有自己的实现原理,SQLserver也有自己的实现原理,如果不去规定一套规范,那就需要写多套程序
JDBC连接步骤:
1.注册驱动(告诉要连接的是哪个品牌的数据库)
MySQL :: MySQL Community Downloads
(73条消息) 在mysql官网上下载连接的jar包_18你磊哥的博客-CSDN博客_mysqljar包
add as library 是将该文件添加到该项目当中
Decomplied .class file,bytecode version:52.0
(73条消息) Class.forName()用法详解_mocas_wang的博客-CSDN博客_class.forname
2.获取连接(表示JVM的进程和数据库的进程打开了)
url =“jdbc:mysql://127.0.0.1:3306/bjpowernode”
url:统一资源定位符,网络当中的绝对路径
localhost和127.0.0.1都可以作为本机的地址
通信协议就是定好的数据传送的格式
3.获取数据库的操作对象(专门执行SQL的对象)
4.执行SQL的语句
5.处理查询结果集
6.释放资源
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBC_test01 {
public static void main(String[] args) throws Exception{
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取链接
Connection connection = DriverManager.getConnection("");
// 3.获取数据库的操作对象
Statement statement = connection.createStatement();
// 4.执行sql
String str = "SELECT * FROM user";
ResultSet resultSet = statement.executeQuery(str);
// 5.处理查询的结果
while(resultSet.next()){
System.out.println(resultSet.getInt(""));
}
// 6.释放资源
connection.close();
statement.close();
resultSet.close();
}
}
执行插入的语句:
mysql> select * from t_vip;
+----+--------------+
| id | name |
+----+--------------+
| 1 | liming |
| 2 | liming |
| 3 | liming |
| 4 | liming |
| 5 | liming |
| 6 | liming |
| 7 | liming |
| 8 | liming |
| 9 | liming |
| 10 | liming |
| 11 | xiaotiantian |
| 12 | duoraimi |
| 13 | xiaomoxian |
| 17 | lihong |
+----+--------------+
14 rows in set (0.01 sec)
package com.bjpowernode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception{
// 1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
/*
是因为我们数据库安装完成后,默认是不能远程登陆的
你只能采用本地登陆 你只能写localhost 或者127.0.0.1
如果你想要远程登陆那你得修改一下数据库,让其能够远程登陆
修改完之后重启一下MySQL服务
*/
// 2.获取链接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bjpowernode","root","wsy");
// 3.获取数据库的操作对象
Statement statement = connection.createStatement();
// 4.执行sql
// String str = "SELECT * FROM EMP";
String str = "insert into t_vip values(18,'qitiandasheng')";
// ResultSet resultSet = statement.executeQuery(str);
int i = statement.executeUpdate(str);
System.out.println(i==1?"插入成功":"插入失败");
// 5.处理查询的结果
System.out.println("处理的数据库是"+connection);
// while(resultSet.next()){
// System.out.println(resultSet.getInt("DEPTNO"));
// }
// 6.释放资源
connection.close();
statement.close();
// resultSet.close();
}
}
mysql> select * from t_vip;
+----+---------------+
| id | name |
+----+---------------+
| 1 | liming |
| 2 | liming |
| 3 | liming |
| 4 | liming |
| 5 | liming |
| 6 | liming |
| 7 | liming |
| 8 | liming |
| 9 | liming |
| 10 | liming |
| 11 | xiaotiantian |
| 12 | duoraimi |
| 13 | xiaomoxian |
| 17 | lihong |
| 18 | qitiandasheng |
+----+---------------+
15 rows in set (0.01 sec)
老杜的代码习惯:
package com.bjpowernode;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc01 {
public static void main(String[] args) {
// 在try里面的变量需要拿出来做null
Connection connection = null;
Statement statement = null;
//哪里有异常烦死了明天再去找
try {
Driver driver = (Driver) new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);
String url = "jdbc:mysql://127.0.0.1:3306/bjpowernode";
connection = DriverManager.getConnection(url, "root", "wsy");
statement = connection.createStatement();
String sql = "insert into t_vip values(19,'lulongqian'),(20,'hanshuai')";
int i = statement.executeUpdate(sql);
System.out.println(i==2?"操作成功":"操作失败");
}catch (Exception e){
}finally {
System.out.println("?");
try {
// 老杜说需要先去关闭最后出来的
statement.close();
}catch (Exception e){
}
try {
connection.close();
}catch (Exception e){
}
}
}
}
删除其中的一条:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc02 {
public static void main(String[] args) {
Connection conn = null;
Statement statement = null;
try{
// 1.
DriverManager.registerDriver( new com.mysql.cj.jdbc.Driver());
// 2.
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bjpowernode", "root", "wsy");
// 3.
statement = conn.createStatement();
// 4
String sql = "delete from t_vip where id=7";
int i = statement.executeUpdate(sql);
System.out.println("删除了"+i+"条");
}catch (SQLException e){
e.printStackTrace();
}finally {
if (statement!=null){
try {
statement.close();}catch (SQLException e){
e.printStackTrace();
}}
if(conn!=null){
try {
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
// 1.注册驱动
// 2.获取连接
// 3.获取执行对象
// 4.执行sql对象
// 5.关闭对象
}
}
JDBC中sql语句不需要写分号
mysql> select * from t_vip;
+----+---------------+
| id | name |
+----+---------------+
| 1 | liming |
| 2 | liming |
| 3 | liming |
| 4 | liming |
| 5 | liming |
| 6 | liming |
| 8 | liming |
| 9 | liming |
| 10 | liming |
| 11 | xiaotiantian |
| 12 | duoraimi |
| 13 | xiaomoxian |
| 17 | lihong |
| 18 | qitiandasheng |
| 19 | lulongqian |
| 20 | hanshuai |
+----+---------------+
16 rows in set (0.03 sec)
另外一种加载驱动的办法 查看源码:
package com.mysql.cj.jdbc;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
可以直接调用类 因为里面是静态代码块儿
Class.Forname(con.mysql.jdbc.Driver)
(73条消息) intelliJ IDEA如何创建.properties文件_程序猫A建仔的博客-CSDN博客_idea怎么创建properties文件
在实际开发中,不建议将数据库的信息写死在java中,往往需要一个bundle ResourceBundle 来储存这些信息放在properties中
package com.bjpowernode;
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ResourceBundle;
public class jdbc03 {
public static void main(String[] args) throws Exception{
ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");
String driver = jdbc.getString("driver");
String url = jdbc.getString("url");
String user = jdbc.getString("user");
String password = jdbc.getString("password");
Class.forName(driver);
// DriverManager.getConnection(url, , )
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "update t_vip set name='dahuang' where id=6";
int i = statement.executeUpdate(sql);
System.out.println(i==1?"chenggong":"shibai");
statement.close();
connection.close();
}
}
rs.next()指向下一行,返回bool类型
jdbc结果查询集:
下面在取出的时候,也可以将String改为int,Double类型,然后接受就直接换位double和int类型了,就可以直接进行算术运算
package com.bjpowernode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ResourceBundle;
public class jdbc04 {
public static void main(String[] args) throws Exception{
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * from t_vip");
while (resultSet.next()){
// 使用数字会显得不是很健壮,使用列名会稍微好一些
// 写列名之后 如果使用SELECT ename as e......则后面的列明要用a因为他是从select里面来查看的
String col1 = resultSet.getString(1);
String col2 = resultSet.getString(2);
System.out.println(col1+"liwai"+col2);
}
resultSet.close();
statement.close();
connection.close();
}
}
用户登陆界面的初始化:
package com.bjpowernode;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;
public class jdbc05 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// 初始化一个用户界面
Map<String,String> userLoginInfo= initUI();
Boolean loginSuccess = loginIn(userLoginInfo);
System.out.println(loginSuccess?"用户登陆成功":"用户登陆失败");
// 验证初始化密码和账号
}
/**
* 用户登录
* @param userLoginInfo
* @return 用户登录信息
* @throws ClassNotFoundException
* @throws SQLException
*/
private static Boolean loginIn(Map<String, String> userLoginInfo) throws ClassNotFoundException, SQLException {
//1.注册驱动
Boolean loginSuccess = false;
Class.forName("com.mysql.cj.jdbc");
//
ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");
String user = jdbc.getString("user");
String url = jdbc.getString("url");
String password = jdbc.getString("password");
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select loginPwd from t_vip where loginName='"+userLoginInfo.get("loginName")+"'");
if(resultSet.next()){
loginSuccess=true;
}
return loginSuccess;
}
/**
*登陆界面设置
* @return 返回用户名的信息
*/
private static Map<String, String> initUI() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入您的用户名");
String loginName = scanner.nextLine();
String loginPwd = scanner.nextLine();
HashMap<String, String> loginInfo = new HashMap<>();
loginInfo.put("loginName", loginName);
loginInfo.put("loginPws", loginPwd);
return loginInfo;
}
}
(77条消息) sql注入详解_山山而川'的博客-CSDN博客_sql注入
mysql> select * from t_vip where id = 'dasa' or '1'='1';
+----+---------------+
| id | name |
+----+---------------+
| 1 | liming |
| 2 | liming |
| 3 | dahuang |
| 4 | liming |
| 5 | liming |
| 6 | dahuang |
| 8 | liming |
| 9 | liming |
| 10 | liming |
| 11 | xiaotiantian |
| 12 | duoraimi |
| 13 | xiaomoxian |
| 17 | lihong |
| 18 | qitiandasheng |
| 19 | lulongqian |
| 20 | hanshuai |
+----+---------------+
16 rows in set (0.03 sec)
sql注入的原因:
输入的语句含有sql的关键字导致原来的sql语句被扭曲
sql注入的解决:
1.将statement修改成Preparedstatement
提前进行编译sql语句,只需要给语句进行赋值即可。关键字不起作用
package com.bjpowernode;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;
public class jdbc06 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// 初始化一个用户界面
Map<String,String> userLoginInfo= initUI();
Boolean loginSuccess = loginIn(userLoginInfo);
System.out.println(loginSuccess?"用户登陆成功":"用户登陆失败");
// 验证初始化密码和账号
}
/**
* 用户登录
* @param userLoginInfo
* @return 用户登录信息
* @throws ClassNotFoundException
* @throws SQLException
*/
private static Boolean loginIn(Map<String, String> userLoginInfo) throws ClassNotFoundException, SQLException {
//1.注册驱动
Boolean loginSuccess = false;
Class.forName("com.mysql.cj.jdbc.Driver");
//通过ResourceBundle获得信息
/**
* 使用 prestatement 来代替statement
*/
ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");
String user = jdbc.getString("user");
String url = jdbc.getString("url");
String password = jdbc.getString("password");
Connection connection = DriverManager.getConnection(url, user, password);
// System.out.println("登陆成功");
String sql = "select * from t_vip where name=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, userLoginInfo.get("loginName"));
ResultSet resultSet = preparedStatement.executeQuery();
// Statement statement = connection.createStatement();
// ResultSet resultSet = statement.executeQuery("select * from t_vip where name='"+userLoginInfo.get("loginName")+"'");
if(resultSet.next()){
loginSuccess=true;
}
preparedStatement.close();
connection.close();
return loginSuccess;
}
/**
*登陆界面设置
* @return 返回用户名的信息
*/
private static Map<String, String> initUI() {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入您的用户名");
String loginName = scanner.nextLine();
String loginPwd = scanner.nextLine();
HashMap<String, String> loginInfo = new HashMap<>();
loginInfo.put("loginName", loginName);
loginInfo.put("loginPws", loginPwd);
return loginInfo;
}
}
statement和prestatement的区别:
prestatement
1解决了sql注入的问题,
2并且prestatement的执行效率要比statement的执行效率要高。在MySQL当中,当一条语句编译之后,后面的一条语句和前面的与语句一样然后进行编译的时候,就不会再去编译了。
3preparedstatemend可以进行安全的检查
单纯进行传值,使用preparedstatement
业务方面要求必须注入sql,必须使用statement
import java.sql.*;
import java.util.ResourceBundle;
import java.util.Scanner;
public class jdbc07 {
public static void main(String[] args) {
System.out.println("请输入desc或asc");
Scanner scanner = new Scanner(System.in);
String next = scanner.next();
ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");
String url = jdbc.getString("url");
String user = jdbc.getString("user");
String password = jdbc.getString("password");
String driver = jdbc.getString("driver");
ResultSet resultSet=null;
Statement statement=null;
Connection connection=null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
String sql = "select * from t_vip order by id "+next;
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getInt("id"));
}
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}finally {
try {
resultSet.close();
}catch (SQLException e){
e.printStackTrace();
}
try {
statement.close();
}catch (SQLException e){
e.printStackTrace();
}
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
使用preparedstatement进行增删改查:
package com.bjpowernode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class jdbc08 {
public static void main(String[] args) {
ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");
String driver = jdbc.getString("driver");
String url = jdbc.getString("url");
String user = jdbc.getString("user");
String password = jdbc.getString("password");
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
String sql = "insert into t_vip values(?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 21);
preparedStatement.setString(2, "qianqian");
int i = preparedStatement.executeUpdate();
System.out.println(i);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
preparedStatement.close();
}catch (SQLException e){
e.printStackTrace();
}
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
事物的提交
conn.setAutoCommit(false);
conn.commit();
conn.rollback();
package com.bjpowernode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class jdbc09 {
/**
* jdbc 自动提交业务代码
* jdbc
*/
public static void main(String[] args) {
ResourceBundle jdbc = ResourceBundle.getBundle("jdbc");
String driver = jdbc.getString("driver");
String url = jdbc.getString("url");
String user = jdbc.getString("user");
String password = jdbc.getString("password");
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
connection.setAutoCommit(false);
String sql = "insert into t_vip values(?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 24);
preparedStatement.setString(2, "qianqian");
int i = preparedStatement.executeUpdate();
preparedStatement.setInt(1, 23);
preparedStatement.setString(2, "qianqian");
int j = preparedStatement.executeUpdate();
System.out.println(j);
connection.commit();
}catch (Exception e){
e.printStackTrace();
try {
connection.rollback();
}catch (Exception es){
es.printStackTrace();
}
}finally {
try {
preparedStatement.close();
}catch (SQLException e){
e.printStackTrace();
}
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
我只能说老杜讲的太牛逼了 通过JDBCUtil进行封装
package com.bjpowernode;
import java.sql.*;
public class JDBCutil {
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
}catch (ClassNotFoundException e){
e.printStackTrace();
}
}
public static Connection getconnection()throws SQLException{
return DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode", "root", "wsy");
}
public static void close(ResultSet rs,Statement stat,Connection conn){
if(rs!=null){
try {
rs.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(stat!=null){
try {
stat.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
package com.bjpowernode;
import java.awt.desktop.SystemEventListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc10 {
public static void main(String[] args) {
ResultSet resultSet=null;
PreparedStatement preparedStatement=null;
Connection connection=null;
try {
connection = JDBCutil.getconnection();
String sql="select * from t_vip where name like '%o%'";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCutil.close(resultSet,preparedStatement,connection);
}
}
}
乐观锁和悲观锁
SELECT id,name from t_vip where id=1 for update;
# 这里的for update将搜索出来的记录锁住了,在事务执行过程中无法去修改,又叫做悲观锁
演示行级锁(悲观锁)
package com.bjpowernode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class jdbc11 {
public static void main(String[] args) {
Connection connection=null;
ResultSet resultSet=null;
PreparedStatement preparedStatement=null;
try {
connection = JDBCutil.getconnection();
connection.setAutoCommit(false);
String sql="SELECT * from t_vip where id=1 for update";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
package com.bjpowernode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class jdbc12 {
public static void main(String[] args) {
PreparedStatement preparedStatement=null;
Connection connection=null;
try {
connection = JDBCutil.getconnection();
String sql="update t_vip set name='wang' where id=1";
preparedStatement = connection.prepareStatement(sql);
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
JDBCutil.close(null, preparedStatement,connection );
}
}
}