1.加载JDBC驱动程序
1 import java.sql.DriverManager;
2 import java.util.Enumeration;
3
4 public class UseMySQLDriver {
5 public static void main(String[] args) {
6 try {
7 // 初始化并加载MySQL驱动程序
8 Class.forName("com.mysql.jdbc.Driver");
9 Enumeration em = DriverManager.getDrivers();
10 // 显示驱动程序信息
11 while (em.hasMoreElements()) {
12 System.out.println(em.nextElement());
13 }
14 // 处理加载数据库中可能出现的异常
15 } catch (Exception e) {
16 System.out.println("加载数据库驱动程序出现异常");
17 }
18 }
19 }
2.通过JDBC对数据库进行查询
1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.ResultSetMetaData;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8
9 public class JDBCSearch {
10 static ResultSet rs = null;
11 public static void main(String[] args) throws SQLException {
12 try {
13 Class.forName("com.mysql.jdbc.Driver");// 加载驱动程序
14 Connection con = DriverManager.getConnection(
15 "jdbc:mysql://localhost:3306/myuser", "root", "root");// 建立链接
16 Statement usest = con.createStatement();// 创建Statement对象
17 usest.setMaxRows(100);// 设置选项
18 ResultSet users = usest.executeQuery("select * from gods");// 执行查询
19 System.out.println("ResultSet MaxRows:" + usest.getMaxRows());
20 System.out.println("Query Time Out:" + usest.getMaxRows() + "\n");
21 String sqlstr = "select * from gods where price>?";// 执行参数查询
22 PreparedStatement ps = con.prepareStatement(sqlstr);// 创建PreparedStatement对象
23 ps.setString(1, "15");
24 rs = ps.executeQuery();
25 ResultSetMetaData rsmd = rs.getMetaData();// 获取结果集中的列名及其类型
26 int cc = rsmd.getColumnCount();
27 System.out.println("ColumnName\t\tColumnType");
28 for (int i = 1; i <= cc; i++) {
29 System.out.println(rsmd.getColumnClassName(i) + "\t"
30 + rsmd.getColumnTypeName(i));
31 }
32 con.close();// 关闭对象
33 } catch (ClassNotFoundException e) {
34 System.out.println(e.getMessage());
35 e.printStackTrace();
36 }
37 }
38 }
39
3.数据更新
1 import java.io.UnsupportedEncodingException;
2 import java.sql.Connection;
3 import java.sql.DriverManager;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7
8 public class JDBCUpdate {
9 public static void main(String[] args) throws SQLException,
10 UnsupportedEncodingException {
11 String name, type, size, strn;
12 String depict, price, offers;
13 try {
14 Class.forName("com.mysql.jdbc.Driver");// 加载驱动程序
15 Connection con = DriverManager.getConnection(
16 "jdbc:mysql://localhost:3306/myuser", "root", "root");// 建立链接
17 Statement st = con.createStatement();// 创建Statement对象
18 strn = "alter table gods add column size varchar(12)";// 向表中添加列
19 st.executeUpdate(strn);
20 strn = "INSERT INTO gods(name, type, depict,price, offers,sizes) VALUES ('Shiseido', 'Cosmetics','meibaibs','280','SOFINA','250ml');";
21 st.executeUpdate(strn);
22 strn = "INSERT INTO gods(name, type, depict,price, offers) VALUES ('Lamp','luminaire','Cartoon images','28','no');";
23 st.executeUpdate(strn);
24 ResultSet rs = st.executeQuery("select * from gods");// 执行查询
25 System.out.println("商品名称\t商品类别\t商品描述\t商品价格\t优惠商品尺码");
26 while (rs.next()) {
27 name = new String(rs.getString("name").getBytes("iso8859-1"),
28 "gb2312");
29 price = new String(rs.getString("price").getBytes("iso8859-1"),
30 "gb2312");
31 depict = new String(rs.getString("depict")
32 .getBytes("iso8859-1"), "gb2312");
33 type = new String(rs.getString("type").getBytes("iso8859-1"),
34 "gb2312");
35 size = rs.getString("size");
36 offers = new String(rs.getString("offers")
37 .getBytes("iso8859-1"), "gb2312");
38 System.out.println(name + " " + type + " " + depict + " "
39 + price + " " + offers + " " + size);
40 }
41 con.close();// 关闭连接
42 } catch (ClassNotFoundException e) {
43 System.out.println(e.getMessage());
44 e.printStackTrace();
45 }
46 }
47 }
48
4.获取数据库的基本信息
1 import java.sql.Connection;
2 import java.sql.DatabaseMetaData;
3 import java.sql.DriverManager;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6
7 public class JDBCTable {
8 public static void main(String[] args) throws SQLException {
9 String ul, namestr, typestr, cstr, strn = null, indexstr, prostr = null;
10 short data;
11 int index, pron;
12 String[] type = { "table" };
13 ul = "jdbc:odbc:useDSN";
14 try {
15 Class.forName("com.mysql.jdbc.Driver");// 加载驱动程序
16 Connection con = DriverManager.getConnection(
17 "jdbc:mysql://localhost:3306/myuser", "root", "root");// 建立链接
18 DatabaseMetaData dbmd = con.getMetaData();// 使用DatabaseMetaData对象获取数据源相关信息
19 ResultSet rs = dbmd.getTables(null, null, null, type);// 获取表中信息的结果集
20 while (rs.next()) {// 输出表的信息
21 namestr = rs.getString("TABLE_NAME");// 创建表名
22 typestr = rs.getString("TABLE_TYPE");// 获取表的类型
23 System.out.println("输出数据源中所有表的信息");// 输出结果集
24 strn = "数据库表名：" + namestr + "" + "表的类型：" + typestr;
25 System.out.println(strn);
26 }
27 rs = dbmd.getColumns(null, null, "gods", "%");// 获取表中列的信息
28 while (rs.next()) {
29 namestr = rs.getString("TABLE_NAME");// 获取表名
30 cstr = rs.getString("COLUMN_NAME");// 获取列名
31 typestr = rs.getString("TYPE_NAME");// 获取列类型
32 data = rs.getShort("DATA_TYPE");// 获取列的SQL类型
33 System.out.println("输出数据库中列的信息");// 输出列信息
34 strn = "表名：" + namestr + "" + "列名：" + cstr + "" + "列类型：" + ""
35 + "列SQL类型：" + data;
36 System.out.println(strn);
37 }
38 rs = dbmd.getIndexInfo(null, null, "stugodsdent", false, false);// 获取索引信息
39 while (rs.next()) {
40 namestr = rs.getString("INDEX_NAME");// 获取索引名
41 index = rs.getInt("TYPE");// 获取索引类型
42 switch (index) {
43 case 0: {
44 indexstr = "没有索引";
45 break;
46 }
47 case 1: {
48 indexstr = "聚集索引";
49 break;
50 }
51 case 2: {
52 indexstr = "哈希表索引";
53 break;
54 }
55 case 3: {
56 indexstr = "其它索引";
57 break;
58 }
59 }
60 strn = "索引名：" + namestr + "" + "索引类型" + index;
61 System.out.println(strn);
62 }
63 rs = dbmd.getProcedures(null, null, "%");// 获取存储过程信息
64 System.out.println("存储过程信息");
65 while (rs.next()) {
66 namestr = rs.getString("PROCEDURE_NAME");// 获取存储过程名称
67 pron = rs.getInt("PROCEDURE_TYPE");
68 switch (pron) {
69 case 0: {
70 prostr = "返回结果未知";
71 break;
72 }
73 case 1: {
74 prostr = "没有返回结果";
75 break;
76 }
77 case 2: {
78 prostr = "有返回结果";
79 break;
80 }
81 }
82 strn = "存储过程名称：" + namestr + "" + "存储过程类型：" + prostr;
83 System.out.println(strn);
84 }
85 rs = dbmd.getProcedureColumns(null, null, "%", "%");// 获取存储过程列信息
86 System.out.println("存储过程列信息");// 输出存储过程列信息
87 while (rs.next()) {
88 namestr = rs.getString("PROCEDURE_NAME");// 获取存储过程名称
89 prostr = rs.getString("COLUMN_NAME");// 获取存储过程类型
90 strn = "存储过程：" + namestr + "" + "存储过程列名：" + prostr;
91 System.out.println(strn);
92 }
93 con.close();// 关闭链接
94 } catch (ClassNotFoundException e) {
95 System.out.println(e.getMessage());
96 e.printStackTrace();
97 }
98 }
99 }
100
5.获取数据库对SQL支持的信息
1 import java.sql.Connection;
2 import java.sql.DatabaseMetaData;
3 import java.sql.DriverManager;
4
5 public class GetDataBaseMessage {
6 public static void main(String[] args) {
7 try {
8 String ul, strn;
9 ul = "";
10 // 加载驱动程序
11 Class.forName("com.mysql.jdbc.Driver");
12 // 建立连接
13 Connection con = DriverManager.getConnection(
14 "jdbc:mysql://localhost:3306/myuser", "root", "root");
15 // 获取数据源相关信息
16 DatabaseMetaData dbmd = con.getMetaData();
17 // 获取数据库支持的数学函数
18 strn = dbmd.getNumericFunctions();
19 System.out.println("数据库支持的数学函数:" + strn);
20 // 获取数据库支持的字符串函数
21 strn = dbmd.getStringFunctions();
22 System.out.println("数据库支持的字符串函数:" + strn);
23 // 获取数据库支持的系统函数
24 strn = dbmd.getSystemFunctions();
25 System.out.println("数据库支持的系统函数:" + strn);
26 // 获取数据库支持的日期时间函数
27 strn = dbmd.getTimeDateFunctions();
28 System.out.println("数据库支持的日期时间函数:" + strn);
29 boolean flag = dbmd.supportsMinimumSQLGrammar();
30 if (flag) {
31 System.out.println("支持ODBC最小SQL语法");
32 } else {
33 System.out.println("不支持ODBC最小SQL语法");
34 }
35 flag = dbmd.supportsCoreSQLGrammar();
36 if (flag) {
37 System.out.println("支持ODBC核心SQL语法");
38 } else {
39 System.out.println("不支持ODBC核心SQL语法");
40 }
41 flag = dbmd.supportsANSI92EntryLevelSQL();
42 if (flag) {
43 System.out.println("支持ANSI92初级SQL语法");
44 } else {
45 System.out.println("不支持ANSI92初级SQL语法");
46 }
47 flag = dbmd.supportsANSI92IntermediateSQL();
48 if (flag) {
49 System.out.println("支持ANSI92中级SQL语法");
50 } else {
51 System.out.println("不支持ANSI92中级SQL语法");
52 }
53 flag = dbmd.supportsANSI92FullSQL();
54 if (flag) {
55 System.out.println("支持ANSI92全集SQL语法");
56 } else {
57 System.out.println("不支持ANSI92全集SQL语法");
58 }
59 } catch (Exception e) {
60 System.out.println(e.getMessage());
61 e.printStackTrace();
62 }
63 }
64 }
65
6.处理访问数据库出现的常见异常情况
1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.SQLException;
4 import java.sql.SQLWarning;
5
6 public class DataBaseException {
7 public static void main(String[] args) {
8 String ul = "";
9 try {
10 Class.forName("com.mysql.jdbc.Drivers");// 加载驱动程序
11 } catch (Exception e) {
12 System.out.println("加载驱动出错");
13 }
14 try {
15 Connection con = DriverManager.getConnection(
16 "jdbc:mysql://localhost:3306", "root", "root");// 建立链接
17 SQLWarning warn = con.getWarnings();// 创建SQLWarning对象
18 int num = 1;
19 while (warn != null) {
20 System.out.println("第" + num + "警告");
21 System.out.println("SQL state" + warn.getSQLState());
22 System.out.println("警告信息" + warn.getMessage());
23 System.out.println("警告代码" + warn.getErrorCode());
24 warn = warn.getNextWarning();
25 num++;
26 }
27 } catch (SQLException e) {
28 while (e != null) {
29 System.out.println("SQL state" + e.getSQLState());
30 System.out.println("错误消息" + e.getMessage());
31 System.out.println("错误代码" + e.getErrorCode());
32 e = e.getNextException();
33 }
34 }
35 }
36 }
37
7.在Servlet中连接数据库
1 import java.io.IOException;
2 import java.io.PrintWriter;
3 import java.sql.Connection;
4 import java.sql.Driver;
5 import java.sql.DriverManager;
6 import java.sql.ResultSet;
7 import java.sql.ResultSetMetaData;
8 import java.sql.Statement;
9
10 import javax.servlet.ServletException;
11 import javax.servlet.http.HttpServlet;
12 import javax.servlet.http.HttpServletRequest;
13 import javax.servlet.http.HttpServletResponse;
14
15 public class ConnectServletDataBase extends HttpServlet {
16 public void doGet(HttpServletRequest request, HttpServletResponse response)
17 throws ServletException, IOException {
18 PrintWriter out;
19 String title = "Simple Servlet Connecting to MySQL DB";
20 response.setContentType("text/html;charset=GB2312");
21 out = response.getWriter();
22 out.println("<html><head><title>");
23 out.println(title);
24 out.println("</title></head><body>");
25 out.println("<h1>" + title + "</h1>");
26 out.println("<p>this is output from SimpleServlet");
27 Driver d;
28 Connection con;
29 Statement stmt;
30 ResultSet resulets;
31 try {
32 d = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance();// 加载MySQL的JDBC驱动
33 con = DriverManager.getConnection(
34 "jdbc:mysql://localhost:3306/myuser", "root", "root");// 创建连接
35 stmt = con.createStatement();// 创建Statement对象
36 String sqlstr = "select * from gods";// 查询数据表gods的sql语句
37 resulets = stmt.executeQuery(sqlstr);// 查询数据表并将查出的结果集赋给ResultSet对象
38 displayResult(resulets, out);// 调用displayResult方法
39 stmt.close();
40 con.close();
41 } catch (Exception e) {
42 out.println("error:" + e.toString());
43 }
44 out.println("</body></html>");
45 out.close();
46 }
47 public void displayResult(ResultSet result, PrintWriter out) {
48 StringBuffer buf = new StringBuffer();// 创建一个StringBuffer对象
49 String temp;
50 try {
51 ResultSetMetaData rsmd = result.getMetaData();// 创建一个ResultSetMetaData对象
52 int numCols = rsmd.getColumnCount();// 获取结果集中的列数
53 int i, rowcount = 0;
54 for (i = 1; i <= numCols; i++) {// 输出字段名
55 if (i > 1)
56 buf.append(",");
57 buf.append(rsmd.getColumnLabel(i));
58 }
59 buf.append("<br>");
60 while (result.next() && rowcount < 100) {// 输出字段名中的数据
61 for (i = 1; i <= numCols; i++) {
62 if (i > 1)
63 buf.append(",");
64 buf.append(result.getString(i));
65 }
66 buf.append("<br>");
67 rowcount++;
68 }
69 out.println("<br>");
70 out.println(buf.toString());
71 result.close();
72 } catch (Exception e) {
73 out.println("error：" + e.toString());
74 return;
75 }
76 }
77 public static void main(String[] args) {
78 // TODO Auto-generated method stub
79 }
80 }
81
8.数据分页显示
1 package page;
2
3 import java.util.Vector;
4
5 public class PageBean {
6 private int curPage; // 当前第几页；
7 private int maxPage; // 共有多少页；
8 private int maxRowCount; // 总共多少行
9 private int rowsPerPage = 3; // 每页有多少行 ，默认为10行
10 private Vector vector = new Vector(); // 用来存放最后的查询结果
11 private String countSql = ""; // 统计总的查询结果数目的sql
12 private String selectSql = ""; // 查询sql语句
13 private String formName = "item2"; // form表单的名字，默认为item
14 public void setFormName(String formName) {
15 this.formName = formName;
16 }
17 public String getFormName() {
18 return this.formName;
19 }
20 public void setRowsPerPage(int rowsPerPage) {
21 this.rowsPerPage = rowsPerPage;
22 }
23 public int getRowsPerPage() {
24 return this.rowsPerPage;
25 }
26 public void setCurPage(int curPage) {
27 this.curPage = curPage;
28 }
29 public int getCurPage() {
30 return this.curPage;
31 }
32 // 设置查询总数的sql语句，当然不是必须的
33 public void setCountSql(String countSql) {
34 this.countSql = countSql;
35 }
36 public String getCountSql() {
37 return this.countSql;
38 }
39 public void setSelectSql(String selectSql) {
40 this.selectSql = selectSql;
41 }
42 public String getSelectSql() {
43 return this.selectSql;
44 }
45 // 返回查询结果的总记录数
46 public void setMaxRowCount() {
47 this.maxRowCount = PageSQL.getInt(getCountSql());
48 }
49 public int getMaxRowCount() {
50 return this.maxRowCount;
51 }
52 // 根据总数计算总共有多少页
53 public void setMaxPage() {
54 this.maxPage = (this.maxRowCount % this.rowsPerPage == 0) ? this.maxRowCount
55 / this.rowsPerPage
56 : this.maxRowCount / this.rowsPerPage + 1;
57 }
58 public int getMaxPage() {
59 return this.maxPage;
60 }
61 // 返回查询的结果
62 // 修改这个方法，根据不同数据库的特点实现分页显示
63 public Vector getResult() {
64 setMaxRowCount(); // 总结果数
65 setMaxPage(); // 总的页数
66 // select * from table limit n,m 查询出从n开始的m条记录
67 String objectSql = this.selectSql + " limit " + (getCurPage() - 1)
68 * getRowsPerPage() + " , " + getRowsPerPage();
69 Vector vector = PageSQL.getQuery(objectSql);
70 System.out.println(vector.size());
71 return vector;
72 }
73 // 修改这个方法可以改变分页的显示样式
74 public String getPage() {
75 StringBuffer sbf = new StringBuffer();
76 // 首先输出页面中js实现的页面跳转
77 sbf.append("<script languange=\"javascript\">").append(
78 "function Jumping(){").append(
79 " document." + getFormName() + ".submit();").append(" return;")
80 .append("}").append("function gotoPage(pagenum){").append(
81 " document." + getFormName()
82 + ".jumpPage.value=pagenum;").append(
83 " document." + getFormName() + ".submit();").append(
84 " return;").append("}").append("</script>");
85 // 在这个可以改变分页显示的样式
86 sbf.append("<center><table>").append("<tr>").append("<td>").append(
87 "每页" + getRowsPerPage() + "行 ").append(
88 "共" + getMaxRowCount() + "行 ").append(
89 "第" + getCurPage() + "页 ")
90 .append("共" + getMaxPage() + "页").append("<br>").append("");
91 if (getCurPage() == 1) {
92 sbf.append(" 首页 上一页");
93 } else {
94 sbf.append("<a href=\"javascript:gotoPage(1)\">首页</a>").append(
95 " <a href=\"javascript:gotoPage("
96 + (getCurPage() - 1) + ")\">上一页</a>");
97 }
98 if (getCurPage() == getMaxPage()) {
99 sbf.append(" 下一页 尾页");
100 } else {
101 sbf.append(
102 "<a href = \"javascript:gotoPage(" + (getCurPage() + 1)
103 + ")\">下一页</a>").append(
104 " <a href = \"javascript:gotoPage("
105 + getMaxPage() + ")\">尾页</a>");
106 }
107 sbf.append(" 转到第").append(
108 "<select name=\"jumpPage\" onchange=\"Jumping()\">");
109 for (int i = 1; i <= getMaxPage(); i++) {
110 if (i == getCurPage()) {
111 sbf.append("<option selected value=\"" + i + "\">" + i
112 + "</option>");
113 } else {
114 sbf.append("<option value=\"" + i + "\">" + i + "</option>");
115 }
116 }
117 sbf.append("</select>页").append("</td>").append("</tr>").append(
118 "</table></center>").append("");
119 return sbf.toString();
120 }
121
122 }
123
1 package page;
2
3 import java.io.IOException;
4
5 import javax.servlet.Filter;
6 import javax.servlet.FilterChain;
7 import javax.servlet.FilterConfig;
8 import javax.servlet.ServletException;
9 import javax.servlet.ServletRequest;
10 import javax.servlet.ServletResponse;
11
12 public class Zh_Filter implements Filter{
13
14 public void destroy() {
15 // TODO Auto-generated method stub
16 }
17 public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
18 //将所有request内的对象设置字符集为gb2312
19 request.setCharacterEncoding("gb2312");
20 //将所有response内的对象设置字符集为gb2312
21 response.setCharacterEncoding("gb2312");
22 //用chain的doFilter处理过滤
23 chain.doFilter(request, response);
24 }
25 public void init(FilterConfig arg0) throws ServletException {
26 // TODO Auto-generated method stub
27 }
28 }
29
1 <%@ page contentType="text/html;charset=gb2312"%>
2 <%@ page import="page.*"%>
3 <jsp:directive.page import="page.PageBean;" />
4 <html>
5 <head>
6 <title>MySQL分页实例</title>
7 </head>
8 <%
9 String countSql = "select count(*) from gods";
10 String jumpPage = (request.getParameter("jumpPage") == null) ? "1"
11 : request.getParameter("jumpPage");
12 System.out.println(jumpPage + " jumpPage");
13 String objSql = "select * from gods";
14 PageBean pjb = new PageBean();
15 pjb.setRowsPerPage(3); //设置每页显示多少行记录
16 pjb.setCurPage(Integer.parseInt(jumpPage)); //设置要跳转到哪一页
17 pjb.setCountSql(countSql); //设置统计所有记录说的sql
18 pjb.setSelectSql(objSql); //设置要查询的sql
19 pjb.setFormName("item2"); //设置form表单的名字
20 java.util.Vector vector = pjb.getResult(); //将查询结果放在vector中
21 %>
22 <body>
23 <form name="item2" method="post" action="page.jsp">
24 <table border="1" width="70%" align="center">
25 <tr>
26 <th>
27 序号
28 </th>
29 <th>
30 商品名称
31 </th>
32 <th>
33 商品类别
34 </th>
35 <th>
36 商品描述
37 </th>
38 <th>
39 商品价格
40 </th>
41 <th>
42 商品尺码
43 </th>
44 <th>
45 优惠
46 </th>
47 </tr>
48 <%
49 int start = (Integer.parseInt(jumpPage) - 1) * pjb.getRowsPerPage()
50 + 1;
51 System.out.println(start + " start");
52 String name = "";
53 String type = "";
54 String depict = "";
55 String price = "";
56 String size = "";
57 String offers = "";
58 out
59 .println("<FONT style='FONT-SIZE:28px;'><B>MySQL数据库实现的分页实例</B></FONT>");
60 // 传入一个查询sql语句然后将结果放入Vector中。以字段名为Key，并通过toUpperCase()将字段名全部转成大字所以像下面这样才能取道正确的值：
61 // hash.get("FIELD");
62 for (int i = 0; i < vector.size() && i < 3; i++) {
63 java.util.Hashtable hash = (java.util.Hashtable) vector
64 .elementAt(i);
65 name = (String) hash.get("NAME"); //通过字段名取数据
66 type = hash.get("TYPE").toString();
67 depict = (String) hash.get("DEPICT");
68 price = (String) hash.get("PRICE");
69 size = (String) hash.get("SIZE");
70 offers = (String) hash.get("OFFERS");
71 %>
72 <tr>
73 <td>
74 <%=start + i%>
75 </td>
76 <td>
77 <%=name%>
78 </td>
79 <td>
80 <%=type%>
81 </td>
82 <td>
83 <%=depict%>
84 </td>
85 <td>
86 <%=price%>
87 </td>
88 <td>
89 <%=size%>
90 </td>
91 <td>
92 <%=offers%>
93 </td>
94 </tr>
95 <%
96 }
97 %>
98 </table>
99 <%=pjb.getPage()%>
100 </form>
101 </body>
102 </html>
103
9.批处理
1 package chp19;
2 import java.sql.Connection;
3 import java.sql.DatabaseMetaData;
4 import java.sql.DriverManager;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8 public class Batch {
9 // 判断数据库是否支持批处理
10 public static boolean support_Batch(Connection con) {
11 try {
12 // 得到数据库的元数据
13 DatabaseMetaData md = con.getMetaData();
14 return md.supportsBatchUpdates();
15 } catch (SQLException e) {
16 e.printStackTrace();
17 }
18 return false;
19 }
20 // 执行一批SQL语句
21 public static int[] startBatch(Connection con, String[] sqls) throws Exception {
22 if (sqls == null) {
23 return null;
24 }
25 Statement sm = null;
26 try {
27 sm = con.createStatement();
28 for (int i = 0; i < sqls.length; i++) {
29 sm.addBatch(sqls[i]); // 将所有的SQL语句添加到Statement中
30 }
31 // 一次执行多条SQL语句
32 return sm.executeBatch();
33 } catch (SQLException e) {
34 e.printStackTrace();
35 } finally {
36 sm.close();
37 }
38 return null;
39 }
40 public static void main(String[] args) throws Exception {
41 System.out.println("没有执行批处理时的数据为：");
42 query();
43 String[] sqls = new String[3];
44 sqls[0] = "UPDATE staff SET depart='Personnel' where name='mali'";
45 sqls[1] = "INSERT INTO staff (name, age, sex,address, depart, worklen,wage) VALUES ('lili', 27, 'w', 'china','Technology','2','2300')";
46 sqls[2] = "DELETE FROM staff where name='marry'";
47 Connection con = null;
48 try {
49 con = getConnection(); // 获得数据库连接
50 boolean Batch_Flag = support_Batch(con); // 判断是否支持批处理
51 System.out.println("支持批处理？ " + Batch_Flag);
52 if (Batch_Flag) {
53 int[] results = startBatch(con, sqls); // 执行一批SQL语句
54 // 分析执行的结果
55 for (int i = 0; i < sqls.length; i++) {
56 if (results[i] >= 0) {
57 System.out.println("语句: " + sqls[i] + " 执行成功，影响了"
58 + results[i] + "行数据");
59 } else if (results[i] == Statement.SUCCESS_NO_INFO) {
60 System.out.println("语句: " + sqls[i] + " 执行成功，影响的行数未知");
61 } else if (results[i] == Statement.EXECUTE_FAILED) {
62 System.out.println("语句: " + sqls[i] + " 执行失败");
63 }
64 }
65 }
66 } catch (ClassNotFoundException e1) {
67 throw e1;
68 } catch (SQLException e2) {
69 throw e2;
70 } finally {
71 con.close(); // 关闭数据库连接
72 }
73 System.out.println("执行批处理后的数据为：");
74 query();
75 }
76 public static Connection getConnection() { // 数据库连接
77 Connection con = null;
78 try {
79 Class.forName("com.mysql.jdbc.Driver"); // 加载Mysql数据驱动
80 con = DriverManager.getConnection(
81 "jdbc:mysql://localhost:3306/myuser", "root", "root"); // 创建数据连接
82 } catch (Exception e) {
83 System.out.println("数据库连接失败");
84 }
85 return con;
86 }
87 public static void query() throws Exception { // 查询所有的数据
88 Connection con = getConnection();
89 Statement st = con.createStatement();
90 ResultSet rs = st.executeQuery("select * from staff");
91 while (rs.next()) {
92 String name = rs.getString("name");
93 int age = rs.getInt("age");
94 String sex = rs.getString("sex");
95 String address = rs.getString("address");
96 String depart = rs.getString("depart");
97 String worklen = rs.getString("worklen");
98 String wage = rs.getString("wage");
99 System.out.println(name + " " + age + " " + sex + " " + address
100 + " " + depart + " " + worklen + " " + wage);
101 }
102 }
103 }
104
10.事务处理
1 package chp19;
2 import java.sql.Connection;
3 import java.sql.DatabaseMetaData;
4 import java.sql.DriverManager;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8 /**
9 * 判断数据库是否支持事务，如果支持，如何实现事务的提交与回滚。
10 * MySQL中如果要使用事物，必须使用InnoDB存储引擎，在创建表时，后面加上ENGINE=InnoDB。
11 * MySQL默认的存储引擎是MyISAM，不支持事物
12 */
13 public class Transaction {
14 //判断数据库是否支持事务
15 public static boolean isTransaction(Connection con) {
16 try {
17 // 得到数据库的元数据
18 DatabaseMetaData md = con.getMetaData();
19 return md.supportsTransactions();
20 } catch (SQLException e) {
21 e.printStackTrace();
22 }
23 return false;
24 }
25 //将一组SQL语句放在一个事务里执行，要某全部执行通过，要某全部不执行
26 public static void Begin_Transaction(Connection con, String[] sqls)
27 throws Exception {
28 if (sqls == null) {
29 return;
30 }
31 Statement sm = null;
32 try {
33 // 事务开始
34 System.out.println("事务开始！");
35 // 设置连接不自动提交，即用该连接进行的操作都不更新到数据库
36 con.setAutoCommit(false);
37 sm = con.createStatement();
38 for (int i = 0; i < sqls.length; i++) {
39 // 执行SQL语句，但是没更新到数据库
40 sm.execute(sqls[i]);
41 }
42 // 提交，立即更新到数据库
43 System.out.println("事务提交！");
44 con.commit();
45 System.out.println("事务结束！");
46 // 事务结束
47 } catch (SQLException e) {
48 try {
49 // 出现异常时，进行回滚，取消前面执行的操作
50 System.out.println("事务执行失败，进行回滚！\n");
51 con.rollback();
52 } catch (SQLException e1) {
53 e1.printStackTrace();
54 }
55 } finally {
56 sm.close();
57 }
58 }
59 public static void main(String[] args) throws Exception {
60 String[] sqls = new String[3];
61 sqls[0] = "UPDATE staff_info SET wage='2500' where name='lucy'";
62 sqls[1] = "INSERT INTO staff_info (name, age, sex,address, depart, worklen,wage)"
63 + " VALUES ('hahaxiao', 27, 'w', 'china','Personnel','3','2000')";
64 // 执行这条语句会引起错误，因为表staff_info不允许name列重复
65 sqls[2] = "INSERT INTO userMess (name,password,nich,compass,sex,address,mail,phone) "
66 + "values ('nini','nini','mali','nini','w','changchun','ml@sina.com','1315588459');";
67 Connection con = null;
68 try {
69 // 获得数据库连接
70 con = getConnection();
71 // 判断是否支持批处理
72 boolean isTransaction = Transaction.isTransaction(con);
73 System.out.println("支持事务？ " + isTransaction);
74 if (isTransaction) {
75 // 执行事务
76 Transaction.Begin_Transaction(con, sqls);
77 }
78 } catch (Exception e) {
79 e.printStackTrace();
80 } finally {
81 con.close(); // 关闭数据库连接
82 }
83 query_staff();
84 query_userMess();
85 }
86 public static Connection getConnection() { // 数据库连接
87 Connection con = null;
88 try {
89 Class.forName("com.mysql.jdbc.Driver"); // 加载Mysql数据驱动
90 con = DriverManager.getConnection(
91 "jdbc:mysql://localhost:3306/myuser", "root", "root"); // 创建数据连接
92 } catch (Exception e) {
93 System.out.println("数据库连接失败");
94 }
95 return con;
96 }
97 public static void query_staff() { // 查询表staff_info
98 Connection conect = getConnection(); // 获取连接
99 System.out.println("表staff_info的全部记录为：");
100 try {
101 String sql = "select * from staff_info"; // 查询数据的sql语句
102 Statement st = (Statement) conect.createStatement(); // 创建Statement对象
103 ResultSet rs = st.executeQuery(sql); // 返回查询数据的结果集
104 while (rs.next()) { // 判断是否还有下一个数据
105 // 根据字段名获取相应的值
106 String name = rs.getString("name");
107 int age = rs.getInt("age");
108 String sex = rs.getString("sex");
109 String address = rs.getString("address");
110 String depart = rs.getString("depart");
111 String worklen = rs.getString("worklen");
112 String wage = rs.getString("wage");
113 System.out.println(name + " " + age + " " + sex + " " + address
114 + " " + depart + " " + worklen + " " + wage);
115 }
116 System.out.println();
117 } catch (SQLException e) {
118 System.out.println("查询数据失败");
119 }
120 }
121 public static void query_userMess() { // 查询表userMess
122 Connection conect = getConnection(); // 获取连接
123 System.out.println("表userMess的全部记录为：");
124 try {
125 String sql = "select * from userMess"; // 查询数据的sql语句
126 Statement st = (Statement) conect.createStatement(); // 创建Statement对象
127 ResultSet rs = st.executeQuery(sql); // 返回查询数据的结果集
128 while (rs.next()) { // 判断是否还有下一个数据
129 // 根据字段名获取相应的值
130 String name = rs.getString("name");
131 String password = rs.getString("password");
132 String nich = rs.getString("nich");
133 String sex = rs.getString("sex");
134 String address = rs.getString("address");
135 String mail = rs.getString("mail");
136 String phone = rs.getString("phone");
137 System.out.println(name + " " + password + " " + nich + " "
138 + sex + " " + address + " " + mail + " " + phone);
139 }
140 System.out.println();
141 } catch (SQLException e) {
142 System.out.println("查询数据失败" + e.getMessage());
143 }
144 }
145 }
146
11.调用存储过程
1 package chp19;
2 import java.sql.CallableStatement;
3 import java.sql.Connection;
4 import java.sql.DatabaseMetaData;
5 import java.sql.DriverManager;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Types;
9 public class Procedure { // 获取、创建、调用数据库的存储过程
10 // 列出数据库中所有的存储过程名
11 public static void getProcedureName(Connection con) {
12 try {
13 DatabaseMetaData md = con.getMetaData(); // 获得数据库的元数据
14 ResultSet resultSet = md.getProcedures(null, null, "%"); // 获得所有的存储过程的描述
15 System.out.println("数据库现有的存储过程名为："); // 显示存储过程名，位于结果集的第三个字段
16 while (resultSet.next()) {
17 String procName = resultSet.getString(3);
18 System.out.print(procName + "\t");
19 }
20 System.out.println();
21 } catch (SQLException e) {
22 e.printStackTrace();
23 }
24 }
25 // 调用存储过程
26 public static void callProcedure(Connection con) throws Exception {
27 CallableStatement cs = null;
28 try {
29 // 调用无参数的存储过程
30 cs = con.prepareCall("{call my_update_proc()}");
31 cs.execute();
32 cs = con.prepareCall("{call my_count_proc1(?)}"); // 调用有一个输出参数的存储过程
33 cs.registerOutParameter(1, Types.INTEGER); // 注册输出参数的类型
34 cs.execute(); // 执行
35 int write_Param = cs.getInt(1); // 获取输出参数的值
36 System.out.println("my_count_proc1() 执行结果：" + write_Param);
37 // 调用有一个输入参数和一个输出参数的存储过程
38 cs = con.prepareCall("{call my_count_proc(?,?)}");
39 // 注册输出参数的类型
40 cs.registerOutParameter(2, Types.INTEGER);
41 // 设置输入参数的值
42 cs.setString(1, "2000");
43 // 执行
44 cs.execute();
45 // 获取输出参数的值
46 write_Param = cs.getInt(2);
47 System.out.println("my_count_proc 执行结果：" + write_Param);
48 } catch (SQLException e) {
49 e.printStackTrace();
50 } finally {
51 cs.close();
52 }
53 }
54 public static void main(String[] args) throws Exception {
55 Connection con = null;
56 try {
57 con = getConnection(); // 获得数据库连接
58 getProcedureName(con); // 列出数据库的所有存储过程名
59 callProcedure(con); // 调用存储过程
60 } catch (Exception e1) {
61 throw e1;
62 } finally {
63 con.close(); // 关闭数据库连接
64 }
65 }
66 public static Connection getConnection() { // 数据库连接
67 Connection con = null;
68 try {
69 Class.forName("com.mysql.jdbc.Driver"); // 加载Mysql数据驱动
70 con = DriverManager.getConnection(
71 "jdbc:mysql://localhost:3306/myuser", "root", "root"); // 创建数据连接
72 } catch (Exception e) {
73 System.out.println("数据库连接失败");
74 }
75 return con;
76 }
77 }
78
12.连接ODBC数据库
1 package chp19;
2 import java.sql.*;
3 import javax.swing.*;
4 import java.awt.*;
5 import java.awt.event.*;
6 import java.util.*;
7 public class JDBC_ODBC extends JFrame {
8 private Connection connection;
9 // 数据库变量定义
10 private Statement statement;
11 private ResultSet resultSet;
12 private JTable table;
13 private JTextArea area;
14 private JButton button;
15 public JDBC_ODBC() {
16 super("输入SQL语句，按查询按钮查看结果"); // 输出标题
17 try {
18 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // 加载odbc数据驱动
19 connection = DriverManager
20 .getConnection("jdbc:odbc:myodbc", "", "");
21 } catch (ClassNotFoundException cnfex) {
22 System.out.println("装载 JDBC/ODBC 驱动程序失败。");
23 cnfex.printStackTrace();
24 System.exit(1);
25 } catch (SQLException sqlex) {
26 System.out.println("连接数据库失败");
27 sqlex.printStackTrace();
28 System.exit(1);
29 }
30 String test = "select * from staff";
31 area = new JTextArea(test, 4, 30);
32 button = new JButton("查询");
33 button.addActionListener(new ActionListener() { // 定义Button监听
34 public void actionPerformed(ActionEvent e) {
35 getTable();
36 }
37 });
38 JPanel topPanel = new JPanel();
39 topPanel.setLayout(new BorderLayout()); // 创建布局界面
40 topPanel.add(new JScrollPane(area), BorderLayout.CENTER);
41 topPanel.add(button, BorderLayout.SOUTH);
42 table = new JTable();
43 Container ct = getContentPane();
44 ct.setLayout(new BorderLayout());
45 ct.add(topPanel, BorderLayout.NORTH);
46 ct.add(table, BorderLayout.CENTER);
47 getTable();
48 setSize(400, 300);
49 show();
50 // 显示窗口
51 }
52 private void getTable() {
53 try {
54 String query = area.getText(); // 从文本域中获取内容
55 statement = connection.createStatement();
56 resultSet = statement.executeQuery(query); // 执行SQL语句
57 QueryResultSet(resultSet); // 显示查询结果
58 } catch (SQLException sqlex) {
59 sqlex.printStackTrace();
60 }
61 }
62 private void QueryResultSet(ResultSet row) throws SQLException {
63 boolean moreRecords = row.next(); // 定位到第一条记录
64 if (!moreRecords) { // 如果没有记录，则显示消息
65 JOptionPane.showMessageDialog(this, "无记录");
66 setTitle("无记录显示");
67 return;
68 }
69 Vector columnHeads = new Vector(); // 声明向量对象并实例化向量
70 Vector vec = new Vector();
71 try {
72 ResultSetMetaData rsm = row.getMetaData();
73 for (int i = 1; i <= rsm.getColumnCount(); ++i)
74 columnHeads.addElement(rsm.getColumnName(i)); // 获取字段的名称
75 do {
76 vec.addElement(getNextRow(row, rsm)); // 获取记录集
77 } while (row.next());
78 // 显示查询结果
79 table = new JTable(vec, columnHeads);
80 JScrollPane scroller = new JScrollPane(table);
81 Container c = getContentPane();
82 c.remove(1);
83 c.add(scroller, BorderLayout.CENTER);
84 c.validate();
85 } catch (SQLException sqlex) {
86 sqlex.printStackTrace();
87 }
88 }
89 private Vector getNextRow(ResultSet row, ResultSetMetaData rsm)
90 throws SQLException {
91 Vector currentRow = new Vector();
92 for (int i = 1; i <= rsm.getColumnCount(); ++i)
93 currentRow.addElement(row.getString(i));
94 return currentRow; // 返回一条记录
95 }
96 public void disconnect() {
97 try {
98 connection.close(); // 关闭数据库连接
99 } catch (SQLException sqlex) {
100 System.out.println("关闭数据库连接失败");
101 sqlex.printStackTrace();
102 }
103 }
104 public static void main(String args[]) {
105 final JDBC_ODBC ben = new JDBC_ODBC();
106 ben.addWindowListener(new WindowAdapter() {
107 public void windowClosing(WindowEvent e) {
108 ben.disconnect();
109 System.exit(0);
110 }
111 });
112 }
113 }
114
13.数据库中图片文件的存取
1 package chp19;
2 import java.io.File;
3 import java.io.FileInputStream;
4 import java.nio.ByteBuffer;
5 import java.sql.Connection;
6 import java.sql.DriverManager;
7 import java.sql.ResultSet;
8 import java.sql.Statement;
9 public class Image_DataBase {
10 public static void main(String[] args) {
11 // 生成测试类
12 try {
13 File file = new File("D:/abc/PIC3.bmp"); // 将保存的图片生成文件对象
14 if (Image_byte(null, file)) // 如果文件成功读入byte数组则返回true。否者返回false
15 System.out.print("ture");
16 else
17 System.out.print("False");
18 } catch (Exception e) {
19 System.out.println("图像生成文件对象失败 " + e.getMessage());
20 }
21 }
22 public static boolean Image_byte(String sqlstr, File file) { // 将图像文件转换成byte[]
23 try {
24 // 将文件对象流化，并在内存生成文件大小的缓存区
25 FileInputStream fin = new FileInputStream(file);
26 // 创建字节缓存区，并分文件大小空间
27 ByteBuffer nbf = ByteBuffer.allocate((int) file.length());
28 byte[] array = new byte[1024];
29 int offset = 0, length = 0;
30 // 存放字节流
31 while ((length = fin.read(array)) > 0) {
32 if (length != 1024)
33 nbf.put(array, 0, length);
34 else
35 nbf.put(array);
36 offset += length;
37 }
38 // 关闭文件流
39 fin.close();
40 byte[] content = nbf.array();
41 System.out.println(content.length);
42 return LoadImage(sqlstr, content);
43 } catch (Exception e) {
44 System.out.println("图像转换成byte数组失败 " + e.getMessage());
45 }
46 return false;
47 }
48 private static boolean LoadImage(String sqlstr, byte[] in) {
49 boolean flag = false;
50 if (sqlstr == null) {
51 sqlstr = "select * from image";
52 try {
53 // 获取连接，生成记录集
54 Connection con = getConnection();
55 Statement stmt = con.createStatement(
56 ResultSet.TYPE_SCROLL_INSENSITIVE,
57 ResultSet.CONCUR_UPDATABLE);
58 ResultSet rs = stmt.executeQuery(sqlstr);
59 // 如果记录存在则更新记录
60 if (rs.next()) {
61 rs.updateBytes(3, in);
62 rs.updateRow();
63 System.out.println(" updateRow");
64 // 否则插入新纪录
65 } else {
66 rs.moveToInsertRow();
67 rs.updateString(2, "01");
68 rs.updateBytes(3, in);
69 rs.insertRow();
70 System.out.println("insertRow");
71 }
72 rs.close();
73 flag = true;
74 // 处理异常
75 } catch (Exception e) {
76 System.out.println("图像存入数据库失败 " + e.getMessage());
77 }
78 // 返回标签
79 return flag;
80 }
81 return flag;
82 }
83 public static Connection getConnection() { // 连接数组库
84 Connection con = null;
85 try {
86 Class.forName("com.mysql.jdbc.Driver"); // 加载Mysql数据驱动
87 con = DriverManager.getConnection(
88 "jdbc:mysql://localhost:3306/myuser", "root", "root"); // 创建数据连接
89 } catch (Exception e) {
90 System.out.println("数据库连接失败 " + e.getMessage());
91 }
92 return con;
93 }
94 }
95
96
1 <%@ page contentType="image/jpeg;charset=GB2312"%>
2 <%@ page import="java.sql.*"%>
3 <%@ page import="java.io.*"%>
4 <%@ page import="com.sun.image.codec.jpeg.*"%>
5 <%@ page import="javax.imageio.*"%>
6 <%@ page import="java.awt.image.*"%>
7 <html>
8 <head>
9 <meta http-equiv="Content-Type" content="image/bmp;charset=GB2312">
10 <title>数据库中图片文件的存取</title>
11 </head>
12 <body>
13 <%
14 String sql = "select * from image where file_name ='01'";
15 Connection conn = null;
16 BufferedInputStream inputImage = null;
17 try {
18 Class.forName("com.mysql.jdbc.Driver"); // 加载Mysql数据驱动
19 conn = DriverManager.getConnection(
20 "jdbc:mysql://localhost:3306/myuser", "root", "root"); // 创建数据连接
21 Statement st = conn.createStatement();
22 ResultSet rs = st.executeQuery(sql);
23 while (rs.next()) {
24 Blob blob = (Blob) rs.getBlob("content");
25 inputImage = new BufferedInputStream(blob.getBinaryStream());
26 }
27 BufferedImage image = null;
28 image = ImageIO.read(inputImage);
29 ServletOutputStream sos = response.getOutputStream();
30 JPEGImageEncoder encoder = JPEGCodec.createJPEGEncoder(sos);
31 encoder.encode(image);
32 inputImage.close();
33 } catch (SQLException e) {
34 e.printStackTrace();
35 } catch (IOException e) {
36 e.printStackTrace();
37 }
38 %>
39 </body>
40 </html>
1 -- Table "gods" DDL
2
3 CREATE TABLE gods (
4 sizes varchar(28) DEFAULT NULL,
5 offers varchar(22) DEFAULT NULL,
6 depict varchar(22) DEFAULT NULL,
7 type varchar(22) DEFAULT NULL,
8 price double(10,0) DEFAULT NULL,
9 name varchar(100) DEFAULT NULL,
10 id int(10) NOT NULL AUTO_INCREMENT,
11 size varchar(12) DEFAULT NULL,
12 PRIMARY KEY (id)
13 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gb2312;
14
15
16 -- Table "staff" DDL
17
18 CREATE TABLE staff (
19 wage varchar(30) DEFAULT NULL,
20 worklen varchar(30) DEFAULT NULL,
21 depart varchar(100) DEFAULT NULL,
22 address varchar(500) DEFAULT NULL,
23 sex varchar(4) DEFAULT NULL,
24 age int(4) DEFAULT NULL,
25 name varchar(55) DEFAULT NULL,
26 id int(12) NOT NULL AUTO_INCREMENT,
27 PRIMARY KEY (id)
28 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gb2312;
29
30
31
32 -- Table "userMess" DDL
33
34 CREATE TABLE usermess (
35 phone varchar(50) DEFAULT NULL,
36 email varchar(100) DEFAULT NULL,
37 address varchar(200) DEFAULT NULL,
38 sex varchar(4) DEFAULT NULL,
39 compass varchar(20) DEFAULT NULL,
40 nich varchar(30) DEFAULT NULL,
41 password varchar(30) DEFAULT NULL,
42 name varchar(100) DEFAULT NULL,
43 id int(10) NOT NULL,
44 PRIMARY KEY (id)
45 ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
46
47 -- Table "image" DDL
48
49 CREATE TABLE image (
50 url varchar(256) DEFAULT NULL,
51 name varchar(100) DEFAULT NULL,
52 id int(10) NOT NULL AUTO_INCREMENT,
53 PRIMARY KEY (id)
54 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gb2312;
55
56
57
58
59
60
61
62
原文出自： http://kellhan.iteye.com/blog/1130125