JDBC连接数据库

JDBC是使用Hibernate的基础，Hibernate是对JDBC复杂操作的封装,简化了大量的SQL语句编写。

JDBC链接数据库步骤:

1. 注册驱动 mysql驱动 oracle驱动

2. 获取连接
   使用DriverManager.getConnection()产生Connection

   连接数据库的url: jdbc:oracle:thin:@localhost:1521:XE
   jdbc:mysql://127.0.0.1:3306/zmysql user password

3. 创建pstmt/stmt对象
   ,如果有占位符替换占位符
4. 执行sql
   int executeUpdate();
   增 删 改Result executeQuery();
   查 execute();
5. 如果有结果集，处理结果集
6. 释放资源
   Connection
   PreparedStatement
   ResultSet
   后创建的先释放

导包：

以连接MySQL数据库为例，先将驱动包Jar包导入：

Student Pojo类:

package com.ssh.jdbc;


import java.io.Serializable;

public class Student implements Serializable {
    private static final long serialVersionUID = 1L;

    private Long id;
    private String name;
    private Integer age;

    public Student() {

    }
    public Student(Long id, String name, Integer age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }


    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }
}

ConnectionFactory:

对于Connection的创建和关闭，每次都是一样的步骤，所以将其提取出来:

package com.ssh.jdbc.common;


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


public class ConnectionFactory {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static{
        driver = "com.mysql.jdbc.Driver"; //驱动包名
        url = "jdbc:mysql://127.0.0.1:3306/ssh"; //ssh代表数据库名
        user = "root";  //数据库用户名密码
        password = "root";
        //从文件系统中获取参数
    }

    /**
     * 获取连接
     * */
    public static Connection getConnection() throws Exception {
        Class.forName(driver);//加载mysql的驱动类
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 释放资源
     * */
    public static void close(ResultSet rs,
            PreparedStatement pstmt ,Connection conn) throws SQLException{
        if(rs!=null){
            rs.close();
        }
        if(pstmt!=null){
            pstmt.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
}

使用时:

package com.ssh.jdbc;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.ssh.jdbc.common.ConnectionFactory;

public class Basic {

    public void save(Student stu){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ConnectionFactory.getConnection();
            System.out.println(conn);
            //3创建pstmt
            String sql = "insert into tbl_student values(null,'"+stu.getName()+"',"+stu.getAge()+")";
            pstmt = conn.prepareStatement(sql);
            //4执行sql
            int num = pstmt.executeUpdate();
            System.out.println("成功插入"+num);
            //5如果有结果集处理结果集
        } catch (Exception e) {
            e.printStackTrace();
        }  finally{
            //6释放资源
            try {
                ConnectionFactory.close(null, pstmt, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void queryAll(){
        try {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            try{
                conn = ConnectionFactory.getConnection();
                //创建pstmt对象
                String sql = "select * from tbl_student";
                pstmt = conn.prepareStatement(sql);
                //执行查询
                rs = pstmt.executeQuery();
                //处理结果集
                while(rs.next()){
                    long id = rs.getLong("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    System.out.println(id+"=="+name+"=="+age);
                }
            }finally{
                //释放资源
                ConnectionFactory.close(rs, pstmt, conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新
     * Student stu (id,name,age)
     * */
    public void update(Student stu){
        try {
            Connection conn = null;
            PreparedStatement pstmt = null;
            try{
                conn = ConnectionFactory.getConnection();
                String sql = "update tbl_student set name = ?,age = ? where id=?";
                //预处理sql
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, stu.getName());
                pstmt.setInt(2, stu.getAge());
                pstmt.setLong(3, stu.getId());
                //执行sql
                int num = pstmt.executeUpdate();
                System.out.println("更新成功"+num+"条");
            }finally{
                ConnectionFactory.close(null, pstmt, conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Basic basic = new Basic();
        Student stu = new Student(1L, "赖瑞", 12);
        //basic.save(stu);
        //basic.queryAll();
        basic.update(stu);
    }
}