JDBC精解--java连接mysql数据库

java与数据库的连接

1.JDBC简介

1.JDBC(Java Data Base Connectivity)是由java编程语言编写的类及接口组成,
利用JDBC可以将JAVA代码连接到oracle、DB2、SQLServer、MYSQL等数据库
从而实现对数据库中的数据操作的目的
2.JDBC是sun公司开发的一套数据库访问编程接口
3.JDBC四个核心类
 * DriverManager 创建连接
 * Connection    连接类
 * Statement     执行SQL语句-->执行查询指令（executeQuery）
 * ResultSet     结果集

2.JDBC连接步骤

 * JDBC连接步骤
 * 1.注册驱动
 * 2.获取连接
 * 3.获取SQL语句的执行对象 Statement
 * 4.执行SQL语句返回结果集 ResultSet
 * 5.处理结果集
 * 6.关闭资源

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

public class text1 {
     public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.注册一个驱动
            Class.forName("com.mysql.jdbc.Driver");//包名+类名
            //2.获取连接对象
            String url = "jdbc:mysql://localhost:3306/myjdbc";//数据库+主机地址+端口+数据库名称
            //3.连接数据库(是个对象)
            Connection connection = DriverManager.getConnection(url, "root", "123456");//一个D..类的get..方法
            //4.获取执行SQL语句的对象
            Statement statement = connection.createStatement();
            //5.执行sql语句 返回结果集
            String sql = "select * from users";
            ResultSet resultSet = statement.executeQuery(sql);
            //6.处理结果集-->输出SQL语句读取结果
            while(resultSet.next()) { //next()读行指针          
                System.out.println(resultSet.getObject(1));//读取第一个数据
                System.out.println(resultSet.getObject(2));
                System.out.println(resultSet.getObject(3));
                System.out.println(resultSet.getObject(4));
                System.out.println(resultSet.getObject(5));
                //注意:查询数据库时 索引是从1开始的
            }
            //7.关闭资源
            resultSet.close();
            statement.close();
            connection.close();

    }
}

3.JDBC增删改查

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

import org.junit.jupiter.api.Test;

public class text2 {
-------------------1.增删方法

      @Test //用于测试方法
      public void test() throws ClassNotFoundException, SQLException {
             //1.注册驱动器
             Class.forName("com.mysql.jdbc.Driver");
             //2.连接数据库(另一种方法)
             String url="jdbc:mysql://localhost:3306/myjdbc?"
                    + "user=root&password=123456";
             Connection connection = DriverManager.getConnection(url);
             //3.获取SQL执行语句
             Statement statement = (Statement) connection.createStatement();
             //4.执行SQL语句,返回结果集
             //插入数据
             String sql = "insert into users values"
                    + "(5,'gl','123','123@qq.com','2000-01-03')";   

             int row = statement.executeUpdate(sql);//executeUpdate 增删改查使用
             System.out.println(row); //打印受影响的行数
             if (row > 0) {
                System.out.println("插入成功");
            }
             //5.关闭资源
             statement.close();
             connection.close();
         }
---------------------------------------------------------------------------------------
             2.查询方法
           @Test
               public void testSelect()throws Exception {
                 //1.注册驱动
                 Class.forName("com.mysql.jdbc.Driver");
                 //2.创建连接数据库
                 String url="jdbc:mysql://localhost:3306/myjdbc?"
                        + "user=root&password=123456";
                 Connection connection = DriverManager.getConnection(url);
                 //3.获取SQL语句执行方法
                 Statement statement = (Statement) connection.createStatement();
                 //4.执行SQL语句,返回结果集
                 //查询
                 String sql = "select id,name,email from users";
                 ResultSet resultSet = statement.executeQuery(sql);
                 System.out.println(resultSet);//返回的是一个地址
                 //处理结果集
                 while(resultSet.next()) {//next()指针读取器
                     System.out.println(resultSet.getObject("id"));
                     System.out.println(resultSet.getObject("name"));
                     System.out.println(resultSet.getObject("email"));
                 }
                 //5.关闭资源
                 resultSet.close();
                 statement.close();
                 connection.close();
          } 
 }

2.使用对象类打印查询结果

2.1创建User类

package com.lanou3g.demo;

import java.sql.Date;

public class User {
        //对象中声明的属性名 尽量和数据库中的字段相同
    private int id;
    public User() {
        super();

    }
    public User(int id, String name, String password, String email, Date birthday) {
            super();
            this.id = id;
            this.name = name;
            this.password = password;
            this.email = email;
            this.birthday = birthday;
        }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + ", birthday="
                + birthday + "]";
    }
    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;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    private String name;
    private String password;
    private String email;
    private Date birthday;

}

2.2创建mysql查询类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/*
 * 连接数据库的异常处理
 */
public class DemoException {
  public static void main(String[] args) {
      ResultSet resultSet = null;
      Statement statement = null;
      Connection connection = null;
    try {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库
        String url = "jdbc:mysql://localhost:3306/myjdbc";
        try {
            connection = DriverManager.getConnection(url, "root", "123456");
        //3.获取SQL执行语句
            statement = connection.createStatement();
        //4.执行SQL语句,返回结果集
            String sql = "select * from users";
            resultSet = statement.executeQuery(sql);
        //5.创建ArrayLits集合读取类型设为User类
            ArrayList<User> list = new ArrayList<>();
            while(resultSet.next()) {
                //创建user对象
                User user = new User();
                user.setId(resultSet.getInt("id"));//user获取返回结果集中的一条id信息
                user.setName(resultSet.getString("name"));//user获取返回结果集中的一条name信息
                user.setPassword(resultSet.getString("password"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setEmail(resultSet.getString("email"));
                //6.将User类中的信息存入集合中
                list.add(user);
            }
            //遍历查看
            for(User user : list) {
                System.out.println(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException("获取连接失败");
        }finally {

            //关闭资源前判断是否为空 防止空指针出现
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (Exception e2) {
                    throw new RuntimeException("关闭失败");
                }
                //加快系统回收速度
                resultSet = null;
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e2) {
                    throw new RuntimeException("关闭失败");
                }
                //加快系统回收速度
                statement = null;
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e2) {
                    throw new RuntimeException("关闭失败");
                }
                //加快系统回收速度
                connection = null;
            }
            //处理结果集(把数据的记录封装到对象中)
            //把对象保存到数组当中
        }
    } catch (ClassNotFoundException e) {
        //停止程序
        throw new RuntimeException("驱动加载失败");
    }
}
}

幅度太长下篇继续