JavaWeb学习总结（二）JDBC

JDBC连接MySQL数据库

1.导入mysql-connector-java-5.1.48.jar

1.1数据库连接 

        

 

 PS：连接数据库之前最好先建立好数据库并且建立好使用的表，进行JDBC的练习。

2.JDBC的基本配置

1.注册驱动

2.获取连接

3.定义SQL

4.获取执行对象

5.执行SQL

6.结果处理

7.释放资源

package JDBC;

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

public class JDBC_Dome {
    public static void main(String[] args) throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url="jdbc:mysql:///mybatis?useSSL=false";
        String username="root";
        String password="1234";
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.定义SQL
        String sql="select * from tb_user";
        //4.获取执行对象
        Statement statement = connection.createStatement();
        //5.执行SQL
        ResultSet resultSet = statement.executeQuery(sql);
        //6.处理结果
        resultSet.next();
        String string = resultSet.getString(5);
        System.out.println(string);
        //7.释放资源
        statement.close();
        connection.close();
    }
}

2.1DriverManager

package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/*
* DriverManager
*   1.注册驱动
*   2.获取数据库连接
*       getConnection(url, username, password)
*       url
*       语法  jdbc:mysql://ip地址(域名):端口号/数据库名称？参数键值对1&参数键值对2....
*       实例  jdbc:mysql://127.0.0.1:3306/data1
*           如果连接本机的MySQL且默认端口号是3306，则url可以简写为：jdbc:mysql:///数据库名称？参数键值对1
*               例子："jdbc:mysql:///data1?useSSL=false"
*           useSSL=false 禁止使用安全连接方式，解决警告提示
*               例子："jdbc:mysql://127.0.0.1:3306/data1?useSSL=false"
* */
public class JDBC_DriverManager {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url="jdbc:mysql:///data1?useSSL=false";
        String username="root";
        String password="1234";
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.定义SQL
        String sql="UPDATE stu SET `name`='王五' WHERE `name`='李四'";
        //4.获取执行对象
        Statement statement = connection.createStatement();
        //5.执行SQL
        int i = statement.executeUpdate(sql);
        //6.处理结果
        System.out.println(i);
        //7.释放资源
        statement.close();
        connection.close();
    }
}

2.2PreparedStatement

        可以利用？作为占位符写SQL，这样会使写的SQL灵活不会写死。

package JDBC;

import Tool.ConnectionMySQL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class JDBC_PreparedStatement {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        //2.连接数据库
        Connection connection = ConnectionMySQL.Connection_MySQL();
        //3.定义SQL   用户登录
        Scanner scanner=new Scanner(System.in);
        String s = scanner.nextLine();
        int i = scanner.nextInt();
        String sql="select * from emp1 where name = ? and id= ?";
        //4.获取SQL对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        
        //5.执行SQL
        //5.1 占位符赋值
        preparedStatement.setString(1,s);
        preparedStatement.setInt(2,i);

        //5.2创建查询对象
        ResultSet resultSet = preparedStatement.executeQuery();

        //6.数据处理  用户登录
        if (resultSet.next()){
            System.out.println("登录成功");
        }
        else {
            System.out.println("登录失败");
        }
/*        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            if (id==i&& Objects.equals(name, s)){
                System.out.println("登录成功");
            }else {
                System.out.println("登录失败");
            }
        }*/
        //7.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

2.3ResultSet

package JDBC;

import Tool.ConnectionMySQL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
/*
*   ResultSet(结果及对象)作用
*   Boolean next();
*       判断当前行是否为有效行
*           true有效行
*           false无效行
*   getObject(参数)
*       参数：int列的标号1开始，String：列的名称
* */
public class JDBC_ResultSet {
    public static void main(String[] args) throws Exception{
//        //1.注册驱动
//        //2.获取连接
        Connection connection = ConnectionMySQL.Connection_MySQL();
        //3.定义sql
        String sql="select * from emp1";
        //4.创建执行对象
        Statement statement = connection.createStatement();
        //5.执行sql
        ResultSet resultSet = statement.executeQuery(sql);
        //6.数据处理
        while (resultSet.next()){
            int anInt1 = resultSet.getInt(1);
            String string = resultSet.getString(2);
            int anInt = resultSet.getInt(3);

            System.out.println(anInt1+" "+string+" "+anInt);
        }
        //7.释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

2.4Connection

package JDBC;

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


/*
*   Connection
*       1.获取执行SQL的对象
*           //普通执行SQL对象
*           createStatement()
*           //预编译SQL的执行对象；防止SQL注入
*           prepareStatement(sql)
*           //执行储存过程的对象
*           prepareCall(sql)
*       2.管理事务
*           //开启事务
*           setAutoCommit(false)    false为手动提交，true为自动提交
*           //提交事务
*           commit();
*           //回滚事务
*           rollback();
* */
public class JDBC_Connection {
    public static void main(String[] args) throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");     //jar包中有驱动可以不写

        //2.获取连接
        String url="jdbc:mysql://127.0.0.1:3306/data1?useSSL=false";
        String username="root";
        String password="1234";
        Connection connection = DriverManager.getConnection(url, username, password);

        //3.定义SQL
        String sql="UPDATE stu SET `name`='王五' WHERE `name`='李四'";
        String sql1="UPDATE emp1 SET age ='80' WHERE name='王五'";
        String sql2="UPDATE emp1 SET age ='90' WHERE name='李四'";
        //4.获取执行对象
        Statement statement = connection.createStatement();
        try {
            //开启事务      //事务开启必须要有
            connection.setAutoCommit(false);
            //5.执行SQL
            int i = statement.executeUpdate(sql1);
            //6.处理结果
            System.out.println(i);
            //int a=10/0;   有一个错误直接回滚事务到一开始
            //5.执行SQL
            int i2 = statement.executeUpdate(sql2);
            //6.处理结果
            System.out.println(i2);
            //提交事务
            connection.commit();
        } catch (SQLException e) {
            //回滚事务
            connection.rollback();
            e.printStackTrace();
        }

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

3.ConnectionMySQL

        对连接数据库的功能进行封装，分装为一个工具类。

package Tool;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

public class ConnectionMySQL {
    private static Connection connection_Druid=null;
    private static Connection connection=null;
    //useServerPrepStmts=true   预编译开启
    private static final String url="jdbc:mysql://127.0.0.1:3306/data1?useSSL=false&useServerPrepStmts=true";
    //登录的用户名
    private static final String username="root";
    //登录的密码
    private static final String password="1234";

    public static Connection Connection_MySQL() throws Exception {
        if (connection==null){
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            connection = DriverManager.getConnection(url, username, password);
        }
        return connection;
    }

    public static Connection Connection_Druid_MySQL() throws Exception {
        if (connection_Druid==null){
            Properties properties = new Properties();
            properties.load(new FileInputStream("D:\\Java\\JDBC\\jdbc\\src\\druid.properties"));
            //4.获取连接池对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            //5.获取数据库连接
            connection_Druid = dataSource.getConnection();
        }
        return connection_Druid;
    }
}