Java-jdbc

麻烦放收发室，谢谢

已于 2023-06-24 14:47:06 修改

阅读量50

点赞数

文章标签： java 开发语言

于 2023-06-23 23:18:01 首次发布

本文链接：https://blog.csdn.net/weixin_52709651/article/details/131351070

版权

jdbc入门程序

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动程序，类名为com.mysql.jdbc.Driver
        Class.forName("com.mysql.jdbc.Driver");
        //准备获得连接的参数
        String url = "jdbc:mysql://127.0.0.1:3306/first";
        String user = "root";
        String password = "AGOC0927";
//准备sql
        String sql="create table jieqian(id int auto_increment primary key,name varchar(10),money varchar(20));";
        String sql2="insert into jieqian(name,money)values(\"小明\",1000),(\"小红\",1000)";
        String sql3="update jieqian set money=1500 where name='小明'";
        //获得连接

        Connection connection = DriverManager.getConnection(url, user, password);
        //获取执行sql的对象
        Statement statement = connection.createStatement();
        //执行sql
        statement.execute(sql);
        statement.execute(sql2);
        statement.execute(sql3);

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


    }
}

DriverManager

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo0 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动程序，类名为com.mysql.jdbc.Driver
        //Class.forName("com.mysql.jdbc.Driver");
        //准备获得连接的参数,url的参数jdbc:mysql为协议://ip:端口号/数据库名,
        //如果ip和端口号为本地和3306，则可简写url省去ip和端口号
        String url2="jdbc:mysql:///first";
        String url = "jdbc:mysql://127.0.0.1:3306/first?useSSL=false";
        String user = "root";
        String password = "AGOC0927";
//准备sql
        String sql="create table  if not exists jieqian (id int auto_increment primary key,name varchar(10),money varchar(20));";
        String sql2="insert into jieqian(name,money)values(\"小明\",1000),(\"小红\",1000)";
        String sql3="update jieqian set money=1500 where name='小明'";
        //获得连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //获取执行sql的对象
        Statement statement = connection.createStatement();
        //执行sql
        statement.execute(sql);
        statement.execute(sql2);
        statement.execute(sql3);
        //释放资源
        statement.close();
        connection.close();
    }
}

//可不写，底层实现的还是DriverManager工具类的静态方法加载驱动
Class.forName("com.mysql.jdbc.Driver");

Connection

事务管理

connection.setAutoCommit(false);
connection.rollback();
connection.commit();

获取执行sql的对象

//防止sql注入
PreparedStatement ps=connection.prepareStatement(sql);

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动程序，类名为com.mysql.jdbc.Driver
        Class.forName("com.mysql.jdbc.Driver");
        //准备获得连接的参数
        String url = "jdbc:mysql://127.0.0.1:3306/first";
        String user = "root";
        String password = "AGOC0927";
//准备sql
        //String sql="create table  if not exists jieqian (id int auto_increment primary key,name varchar(10),money varchar(20));";
        //String sql2="insert into jieqian(name,money)values(\"小明\",1000),(\"小红\",1000)";
        String sql3="update jieqian set money=4000 where name='小明'";
        String sql4="update jieqian set money=4000 where name='小红'";
        //获得连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //获取执行sql的对象
        Statement statement = connection.createStatement();
        //执行sql

        try {
            connection.setAutoCommit(false);
            int i = statement.executeUpdate(sql3);
            System.out.println(i);
            //int l=3/0;
            int i1 = statement.executeUpdate(sql4);
            System.out.println(i1);
            connection.commit();
        } catch (Exception e) {
            connection.rollback();
            e.printStackTrace();
        }
        //释放资源
        statement.close();
        connection.close();
    }
}

Statement

作用：执行语句

//执行DDL成功也可能返回0，执行DML会返回受影响的行数
statement.executeUpdate(sql)//可执行DML(数据操作语言)，DDL(数据定义语言)

statement.executeQuery(sql)//执行DQL会返回ResultSet对象
//可以用ResultSet的next方法判断查询到的结果是否有下一个，该方法会返回boolean，ResultSet另一个方法为getInt(),参数为列名String或列的列序int，返回值分别为String和int。

ResultSet

package com.hm.jdbc;
import com.hm.pojo.Account;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo_ResultSet {
    public static void main(String[] args) throws SQLException {
        String url="jdbc:mysql://127.0.0.1:3306/first";
        String user="root";
        String password="AGOC0927";
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql="select*from jieqian;";
        ResultSet resultSet = statement.executeQuery(sql);
        List <Account>list=new ArrayList();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name=resultSet.getString(2);
            String money=resultSet.getString("money");
            Account account=new Account();
            account.setId(id);
            account.setMoney(money);
            account.setName(name);
            list.add(account);
        }
        System.out.println(list);
    }
}

在这里插入图片描述

idea删除空白行

Ctrl+R打开替换
用^\s*\n取Replace All即可

sql注入

package com.hm.jdbc;
import org.junit.Test;
import java.sql.*;
public class JDBCDemo_SQLInject {
    @Test
    public void SQLInject() throws SQLException, ClassNotFoundException {
        String url="jdbc:mysql://127.0.0.1:3306/first";
        String user="root";
        String password="AGOC0927";
        Connection connection = DriverManager.getConnection(url, user, password);
        String name="小明";
        String pwd="'or'1'='1";
        String sql="select*from jieqian where name='"+name+"'and money='"+pwd+"'";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登陆失败");
        }
    }
}

通过sql语句的’结束创造or达到where全响应，导致ResultSet对象把查询到的数据全部返回，登陆成功。

防止sql注入

会’进行转义

/**Connection类对象的prepareStatement方法获得PreparedStatement对象，
再调用PreparedStatement类对象的setInt(),参数为index，从1开始，第二个参数为值。**/
String sql="select * from jieqian where id=?and name=?"
PreparedStatement preparedstatement=conn.prepareStatement(sql);
preparedstatement.setInt(1,4000);
preparedstatement.setString(2,"小明");
ResultSet resultset=preparedstatement.executeQuery();

package com.hm.jdbc;

import org.junit.Test;

import java.sql.*;

public class JDBCDemo_pstmt {
    @Test
    public void test() throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/first";
        String user = "root";
        String password = "AGOC0927";
        Connection connection = DriverManager.getConnection(url, user, password);
        String name = "小明";
        String pwd = "'or'1'='1";
        String sql = "select*from jieqian where name=?and money=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pwd);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登陆失败");
        }
    }
}

在mysql目录下与bin同级的my.ini中加入下面代码可打开日志

log-output=FILE
general-log=1
general_log_file=“E:\mysql\mysql.log”
slow-query-log=1
slow_query_log_file=“E:\mysql\mysql_slow.log”
long_query_time=2

两条语句只会预编译一次，（第一条），在创建PreparedStatement对象时就已经预编译，之后会将每条sql执行。

        String url = "jdbc:mysql://127.0.0.1:3306/first?useServerPrepStmts=true";

package com.hm.jdbc;

import org.junit.Test;

import java.sql.*;

public class JDBCDemo_pstmt {
    @Test
    public void test() throws SQLException, InterruptedException {
        String url = "jdbc:mysql://127.0.0.1:3306/first?useServerPrepStmts=true";
        String user = "root";
        String password = "AGOC0927";
        Connection connection = DriverManager.getConnection(url, user, password);
        String name = "小明";
        String pwd = "'or'1'='1";
        String sql = "select*from jieqian where name=?and money=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        Thread.sleep(10000);
        ResultSet resultSet=null;
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pwd);

        resultSet = preparedStatement.executeQuery();

        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pwd);

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

数据库连接池

droid

package com.hm.druid;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileReader;
import java.sql.Connection;
import java.util.Properties;

public class DruidDemo {
    public static void main(String[] args) throws Exception {
//加载配置文件
        Properties properties=new Properties();
        properties.load(new FileInputStream("jdbc-demo1/src/main/droid.properties"));
        //创建数据源
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println(System.getProperty("user.dir"));//打印当前所在的目录

    }
}