java与MySQL数据库的连接

java与MySQL数据库的连接

1.数据库的安装和建立参见上一篇博客中的第1,2步骤。

或使用SQL语句

[sql] view plaincopy​​​​​​​​

1. 1. 
      
2. # ubuntu Linux
3. sudo mysql -u root -p
4. #Windows 7(mysql.exe)
5. 
   
6. create database testdb;
7. use testdb;
8. CREATE TABLE `name_table` (
9. `_id` int(11) NOT NULL,
10. `name` varchar(32) CHARACTER SET utf8,
11. `age` int(11) NOT NULL,
12. `work` varchar(32) CHARACTER SET utf8,
13. `others` varchar(512) CHARACTER SET utf8 DEFAULT NULL,
14. PRIMARY KEY (`_id`)
15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    1. 
       

2.Eclipse的配置。

导入包mysql-connector-java-5.0.5-bin.jar

3.java代码的编写

[java code]

[java] view plaincopy​​
​​​​​​

1. 1. 
      
2. /*
3. *@author: ZhengHaibo
4. *web: blog.csdn.net/nuptboyzhb
5. *mail: [e-mail]
6. *2012-10-6 Nanjing njupt
7. */
8. import java.sql.Connection;
9. import java.sql.DriverManager;
10. import java.sql.PreparedStatement;
11. import java.sql.ResultSet;
12. import java.sql.SQLException;
13. public class helloworld {
14. private Connection conn = null;
15. PreparedStatement statement = null;
16. // connect to MySQL
17. void connSQL() {
18. String urle = "jdbc:mysql://localhost:3306/testdb";//port：3306 database:testdb
19. String username = "root";//user
20. String password = "931706659";//password
21. try {
22. Class.forName("com.mysql.jdbc.Driver" );//加载驱动，连接数据库
23. conn = DriverManager.getConnection(urle,username, password );
24. }
25. //捕获加载驱动程序异常
26. catch ( ClassNotFoundException cnfex ) {
27. System.err.println(
28. "装载 JDBC/ODBC 驱动程序失败。" );
29. cnfex.printStackTrace();
30. }
31. //捕获连接数据库异常
32. catch ( SQLException sqlex ) {
33. System.err.println( "无法连接数据库" );
34. sqlex.printStackTrace();
35. }
36. }
37. 
    
38. // disconnect to MySQL
39. void deconnSQL() {
40. try {
41. if (conn != null)
42. conn.close();
43. } catch (Exception e) {
44. System.out.println("关闭数据库问题 ：");
45. e.printStackTrace();
46. }
47. }
48. 
    
49. // execute selection language
50. ResultSet selectSQL(String sql) {
51. ResultSet rs = null;
52. try {
53. statement = conn.prepareStatement(sql);
54. rs = statement.executeQuery(sql);
55. } catch (SQLException e) {
56. e.printStackTrace();
57. }
58. return rs;
59. }
60. 
    
61. // execute insertion language
62. boolean insertSQL(String sql) {
63. try {
64. statement = conn.prepareStatement(sql);
65. statement.executeUpdate();
66. return true;
67. } catch (SQLException e) {
68. System.out.println("插入数据库时出错：");
69. e.printStackTrace();
70. } catch (Exception e) {
71. System.out.println("插入时出错：");
72. e.printStackTrace();
73. }
74. return false;
75. }
76. //execute delete language
77. boolean deleteSQL(String sql) {
78. try {
79. statement = conn.prepareStatement(sql);
80. statement.executeUpdate();
81. return true;
82. } catch (SQLException e) {
83. System.out.println("插入数据库时出错：");
84. e.printStackTrace();
85. } catch (Exception e) {
86. System.out.println("插入时出错：");
87. e.printStackTrace();
88. }
89. return false;
90. }
91. //execute update language
92. boolean updateSQL(String sql) {
93. try {
94. statement = conn.prepareStatement(sql);
95. statement.executeUpdate();
96. return true;
97. } catch (SQLException e) {
98. System.out.println("插入数据库时出错：");
99. e.printStackTrace();
100. } catch (Exception e) {
101. System.out.println("插入时出错：");
102. e.printStackTrace();
103. }
104. return false;
105. }
106. // show data in ju_users
107. void layoutStyle2(ResultSet rs) {
108. System.out.println("-----------------");
109. System.out.println("执行结果如下所示:");
110. System.out.println("-----------------");
111. System.out.println(" id" + "\t\t" + "name" +"\t\t" + "age" + "\t\t" +"work"+ "\t\t" + "others");
112. System.out.println("-----------------");
113. try {
114. while (rs.next()) {
115. System.out.println(rs.getInt("_id") + "\t\t"
116. + rs.getString("name") + "\t\t"
117. +rs.getInt("age") + "\t\t"
118. + rs.getString("work")+ "\t\t"
119. + rs.getString("others"));
120. }
121. } catch (SQLException e) {
122. System.out.println("显示时数据库出错。");
123. e.printStackTrace();
124. } catch (Exception e) {
125. System.out.println("显示出错。");
126. e.printStackTrace();
127. }
128. }
129. 
     
130. public static void main(String args[]) {
131. 
     
132. helloworld h = new helloworld();
133. h.connSQL();
134. String s = "select * from name_table";
135. 
     
136. String insert = "insert into name_table(_id,name,age,work,others) values("+15+",'csdn',"+24+",'M.S.','nupt')";
137. String update = "update name_table set age =19 where name= 'zhb'";
138. String delete = "delete from name_table where name= 'csdn'";
139. 
     
140. if (h.insertSQL(insert) == true) {
141. System.out.println("insert successfully");
142. ResultSet resultSet = h.selectSQL(s);
143. h.layoutStyle2(resultSet);
144. }
145. if (h.updateSQL(update) == true) {
146. System.out.println("update successfully");
147. ResultSet resultSet = h.selectSQL(s);
148. h.layoutStyle2(resultSet);
149. }
150. if (h.insertSQL(delete) == true) {
151. System.out.println("delete successfully");
152. ResultSet resultSet = h.selectSQL(s);
153. h.layoutStyle2(resultSet);
154. }
155. h.deconnSQL();
156. }
157. }
     1. 
        

4.实验结果

[image]