jdbc

1,连接mysql数据库

package com.lovo;

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

public class Test {
    public static void main(String[] args) {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            //jdbc:mysql://是驱动协议，就像http：//一样
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", "root", "111");//创建连接
            Statement stmt = con.createStatement();//创建语句
            String sql = "SELECT emp.* from emp INNER JOIN " +
            " (select DISTINCT emp.sal as sal from emp where job <> '总裁' order by emp.sal desc limit 0,3) as t1 "+
            " on emp.sal=t1.sal order by emp.sal";
            ResultSet rs = stmt.executeQuery(sql);//发送sql语句得到结果集（游标）
            while (rs.next()) {
                System.out.println("姓名：" + rs.getString("ename") + "\t工资：" + rs.getString("sal"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2，批量插入数据

package com.lovo;

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


public class Test02 {
    public static void main(String[] args) {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", "root", "111");
            String sql = "insert into dept value(?,?,?)";
            con.setAutoCommit(false);//设置事物部自动提交
            PreparedStatement ps = con.prepareStatement(sql);
            for (int i = 0; i < 5; i++) {
                ps.setInt(1, i);
                ps.setString(2, "科研部" + i);
                ps.setString(3, "四川成都");
                ps.addBatch();
            }
            int[] infl = ps.executeBatch();
            con.commit();//提交事物
            for (int i : infl) {
                System.out.print(i + "  ");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                con.rollback();//回滚事物
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally{
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}