java JDBC连接mysql数据库

前言

java连接数据库的学习笔记，我不得不承认java代码的繁杂。

概念

1 创建数据库链接的参数

    String driver;
    String url;
    String user;
    String password;

2 加载数据库

class.formName(driver)

3 获取数据库链接

Connection conn =
    DriverManager
    .getConnection(url,user,password);

4 编写sql语句
创建执行sql语句的对象

PreparedStatement stmt =
    conn.prepareStatement(sql);

5 执行sql语句

• 修改操作

int row = stmt.executeUpdate();

• 执行查询操作

ResultSet rst = stmt.executeQuery();

6 处理结果

• 修改操作，提示执行结果
• 查询操作，变量结果集获取数据

while(rs.next){
    rs.getString("列名");
}

代码实现

代码实例:

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

public class Main {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        insert();
        update();
    }

    //插入操作
    public static void insert(){
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qiandao";
        String user = "root";
        String password = "root";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager.getConnection(url,user,password);
            if(conn!=null)
            {
                System.out.println("连接成功!");
            }
            String sql = "insert into t_sign_info"
                    + "(stu_id,sign_date,sign_in,sign_out,sign_status)"
                    +" values(?,curdate(),curdate(),null,0)";
            stmt = 
                    conn.prepareStatement(sql);
            stmt.setObject(1, 2);
            int row = stmt.executeUpdate();
            if(row>0){
                System.out.println("签到成功");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
    //修改操作

    public static void update(){
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qiandao?UseUncode=true&charsacterEncoding=utf-8";//处理编码
        String user = "root";
        String password = "root";
        try {
            Class.forName(driver);//反射 可以访问类的私有属性
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager
                    .getConnection(url,user,password);
            String sql = "update t_stu_info"
                    + " set stu_name = ?,stu_pwd = ?"
                    + " where stu_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setObject(1, "王五");
            stmt.setObject(2, "999999");
            stmt.setObject(3, 1);
            int row = stmt.executeUpdate();
            System.out.println(row);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }   
    }
}

封装jdbc

(实现增删改)

//封装jdbc操作
    public static int executeUpdate(String sql,Object...objs){
        int row = 0;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qiandao?UseUncode=true&charsacterEncoding=utf-8";
        String user = "root";
        String password = "root";
        try {
            Class.forName(driver);//反射 可以访问类的私有属性
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager
                    .getConnection(url,user,password);
            stmt = conn.prepareStatement(sql);
            if(objs != null){//说明sql语句中有?
                for (int i = 0; i < objs.length; i++) {
                    stmt.setObject(i+1, objs[i]);
                }
            }
            row = stmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return row;
    }

方法调用

String sql = "insert into t_stu_info"
                +" (stu_no,stu_name,stu_pwd)"
                +" value(?,?,?)";//构建sql语句
        int n = executeUpdate(sql, "S1003","赵六","123456");//方法调用
        if(n>0){
            System.out.println("成功");
        }else{
            System.out.println("失败");
        }

(实现查询操作)

public static void executeQuery(String sql,Object...objs){
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qiandao?UseUncode=true&charsacterEncoding=utf-8";
        String user = "root";
        String password = "root";
        try {
            Class.forName(driver);//反射 可以访问类的私有属性
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rst = null;
        try {
            conn = DriverManager
                    .getConnection(url,user,password);
            stmt = conn.prepareStatement(sql);
            if(objs != null){
                for (int i = 0; i < objs.length; i++) {
                    stmt.setObject(i+1, objs[i]);
                }
                rst = stmt.executeQuery();
//              获取查询语句中列的信息
                ResultSetMetaData meta = 
                        rst.getMetaData();
//              获取列的数量
                int count = meta.getColumnCount();
//              System.out.println(count);
                while(rst.next()){
                    for (int i = 0; i < count; i++) {
                        String str = rst.getString(i+1);
                        System.out.print(str+"\t");
                    }
                    System.out.println();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }   
    }

方法调用:

String sql = "select * from t_stu_info";
        executeQuery(sql);