jdbc

浏览数：16 / 时间：2015年06月12日

1,连接mysql数据库

 1 package com.lovo;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 
 9 public class Test {
10     public static void main(String[] args) {
11         Connection con = null;
12         try {
13             Class.forName("com.mysql.jdbc.Driver");//加载驱动
14             //jdbc:mysql://是驱动协议，就像http：//一样
15             con = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", "root", "111");//创建连接
16             Statement stmt = con.createStatement();//创建语句
17             String sql = "SELECT emp.* from emp INNER JOIN " +
18             " (select DISTINCT emp.sal as sal from emp where job <> ‘总裁‘ order by emp.sal desc limit 0,3) as t1 "+
19             " on emp.sal=t1.sal order by emp.sal";
20             ResultSet rs = stmt.executeQuery(sql);//发送sql语句得到结果集（游标）
21             while (rs.next()) {
22                 System.out.println("姓名：" + rs.getString("ename") + "\t工资：" + rs.getString("sal"));
23             }
24         } catch (ClassNotFoundException e) {
25             e.printStackTrace();
26         } catch (SQLException e) {
27             e.printStackTrace();
28         }finally{
29             if (con != null) {
30                 try {
31                     con.close();
32                 } catch (SQLException e) {
33                     e.printStackTrace();
34                 }
35             }
36         }
37     }
38 }

2，批量插入数据

 1 package com.lovo;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 
 9 
10 public class Test02 {
11     public static void main(String[] args) {
12         Connection con = null;
13         try {
14             Class.forName("com.mysql.jdbc.Driver");
15             con = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", "root", "111");
16             String sql = "insert into dept value(?,?,?)";
17             con.setAutoCommit(false);//设置事物部自动提交
18             PreparedStatement ps = con.prepareStatement(sql);
19             for (int i = 0; i < 5; i++) {
20                 ps.setInt(1, i);
21                 ps.setString(2, "科研部" + i);
22                 ps.setString(3, "四川成都");
23                 ps.addBatch();
24             }
25             int[] infl = ps.executeBatch();
26             con.commit();//提交事物
27             for (int i : infl) {
28                 System.out.print(i + "  ");
29             }
30         } catch (ClassNotFoundException e) {
31             e.printStackTrace();
32         } catch (SQLException e) {
33             try {
34                 con.rollback();//回滚事物
35             } catch (SQLException e1) {
36                 e1.printStackTrace();
37             }
38             e.printStackTrace();
39         }finally{
40             if (con != null) {
41                 try {
42                     con.close();
43                 } catch (SQLException e) {
44                     e.printStackTrace();
45                 }
46             }
47         }
48     }
49 }