JDBC简单操作Demo

JDBC: Java DataBase Connectivity

本文主要介绍Java操作MySQL基本套路, 包括链接,增删改查.
详细见注释

import java.sql.*;
import java.sql.Connection;
import java.sql.PreparedStatement;

import com.mysql.jdbc.*;
import com.mysql.jdbc.Driver;

/**
 * Created by butter on 16-11-19.
 */

/**
 *
 * Student类,与数据表相同
 * 属性: Id, Name, Sex, Age
 * 方法: get(),set()
 */

class Student {
    private String Id;
    private String Name;
    private String Sex;
    private String Age;

    Student(String Name, String Sex, String Age) {
        this.Id = null; //default
        this.Name = Name;
        this.Sex = Sex;
        this.Age = Age;
    }

    public String getId() {
        return Id;
    }

    public void setId(String Id) {
        this.Id = Id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String Name) {
        this.Name = Name;
    }

    public String getSex() {
        return Sex;
    }

    public void setSex(String Sex) {
        this.Sex = Sex;
    }

    public String getAge() {
        return Age;
    }

    public void setage(String Age) {
        this.Age = Age;
    }
}

/**
 *
 * 准备工作:(略)
 * (1)创建demo数据库
 * (2)建立students表,列:Id(auto increment), Name, Sex, Age
 *
 */
public class demo_BasicAction {

    //函数功能:链接数据库,返回Connection实例
    private static Connection getConn() {
        String driver = "com.mysql.jdbc.Driver";
//      String url = "jdbc:mysql://localhost:3306/demo";//mysql版本较高,报SSL警告
        String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String username = "root";
        String password = "qw#$%ersd*N(fhbsduoirt%^&yh,ml;dsm;lv%^&*IObnszfvb,.asd";
        Connection conn = null;


        /**(1)
         * 要获取数据库联机必须有一下几个动作:
         * (1)注册Driver操作对象
         * (2)取得Connection操作对象
         * (3)关闭Connection操作对象
         *
         * 其中(1)使用JDBC时,要求加载.class文档的方式有4种
         * (1)使用Class.getName();
         * (2)自行建立Driver接口操作类的实例(registerDriver)
         * (3)启动JVM时,指定jdbc.drivers属性
         * (4)设定JAR中/services/java.drivers属性
         */

        /**(2)
         * Connection操作对象:
         * Connection接口的操作对象时数据库联机代表对象,要取得Connection操作对象,可以通过DriverManager的getConnection();
         * Connection conn = DriverManager.getConnection(jdbcURL, username, password);
         *
         * jdbcURL:定义了链接数据库时的协议,子协议,数据源识别()
         * mysql为例
         * 子协议名称:mysql
         * 数据源识别主要 标出数据库地址,端口号, 名称, 用户, 密码
         *
         */

        /**
         * (3)
         * SQLException: 数据库操作过程中发生错误的代表对象,
         * SQLExecption是受检异常(Checked Exception)必须TryCatchFinally
         * 在异常时关闭相关资源
         *
         */

        //驱动注册:DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //实际上很少自己撰写上述代码进行动作,只通过forName()就可以进行动态加载驱动程序类

        try {
            Class.forName(driver); //classLoader,加载对应驱动//(1)
            conn = (Connection) DriverManager.getConnection(url, username, password);//进行链接(2)
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {//这异常要经常处理!(3)
            e.printStackTrace();
        }
        return conn;//返回Connection对象
    }

    /**
     * 以下是数据的增删改查操作,很好理解
     * 主要就是Statement类,主要就是用来执行sql语句
     * PreparedStatement是预先编译好的sql语句,一般用这个,比较方便
     * 当参数会变动的部分,先指定"?"占位,如下(4)
     * 然后再调 setString() / setInt()
     *
     * 执行语句: statement.executeUpdate();
     * 还有一个clearParameters()方法,用来清除设置的参数,之后就可以再次使用这个实例
     */

    //增
    private static int insert(Student student) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into students (Name,Sex,Age) values(?,?,?)";//(4)
        //创建PreparedStatment实例
        PreparedStatement pstmt;
        try {
            //设置sql语句
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getSex());
            pstmt.setString(3, student.getAge());
            //执行
            i = pstmt.executeUpdate();//这玩意应该是返回affected row(s) 受影响列
            pstmt.close();//关
            conn.close();//关
        } catch (SQLException e) {//处理异常
            e.printStackTrace();
        }
        return i;
    }

    /**
     *
     * @param student
     * @return
     * 改
     */

    private static int update(Student student) {

        Connection conn = getConn();
        int i = 0;
        String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    private static Integer getAll() {
        Connection conn = getConn();
        String sql = "select * from students";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
             System.out.println("+----------------------------+");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                }
                System.out.println("");
            }
             System.out.println("+----------------------------+");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
    *删
    */

    private static int delete(String name) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete from students where Name='" + name + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }



    public static void main(String args[]) {
        System.out.println("getall:");
        demo_BasicAction.getAll();

        System.out.println("insert:");
        demo_BasicAction.insert(new Student("A", "Male", "1"));
        demo_BasicAction.insert(new Student("B", "Male", "2"));
        demo_BasicAction.insert(new Student("C", "Male", "3"));

        System.out.println("getall:");
        demo_BasicAction.getAll();

        System.out.println("update:");
        demo_BasicAction.update(new Student("B", "", "7"));

        demo_BasicAction.delete("C");


        System.out.println("getall:");
        demo_BasicAction.getAll();
    }
}