Windows程序获取mysql数据_Windows下Java程序实现对Mysql数据库的插入，删除，修改和查询...

1 //import java.io.*;

2 import java.util.*;3 import java.sql.*;4

5 /**

6 *7 * @date 2014-05-208 *@authorasif9 * @作用 java实现对mysql数据库的增减删查11 *12 */

13

14 public classZGSC {15 public static voidmain(String args[])16 {17 try

18 {19 //连接数据库

20 Connection conn =MysqlConnection();21 //conn.close();

22 System.out.println("Connect database success,Please select :");23 System.out.println("1.(list)\t 2.(insert)\t 3.(delete)\t 4.(update)\t 0.(exit)");24 System.out.println("----------------------------------------------------------------------------------");25 Scanner in = newScanner(System.in);26 while(in.hasNext())27 {28 int Case =in.nextInt();29 switch(Case)30 {31 case 1:32 Listsql(conn);33 break;34 case 2:35 Insertsql(conn);36 break;37 case 3:38 Deletesql(conn);39 break;40 case 4:41 Updatesql(conn);42 break;43 case 0:44 conn.close(); //退出时关闭数据库

45 System.out.println("Program exit,goodbye!");46 return;47 default:48 System.out.println("Your input error,Please input again");49 }50 }51 in.close();52 }53 catch(Exception e)54 {55 System.out.println("Cant't connect mysql database!");56 e.getStackTrace();57 }58 }59

60 //连接数据库函数

61 public staticConnection MysqlConnection()62 {63 String username = "root";64 String userpasswd = "123456";65 String url = "jdbc:mysql://127.0.0.1:3306/test";66 try

67 {68 Class.forName("com.mysql.jdbc.Driver").newInstance();69 try

70 {71 Connection contemp =DriverManager.getConnection(url, username, userpasswd);72 returncontemp;73 }74 catch(SQLException e)75 {76 //TODO Auto-generated catch block

77 e.printStackTrace();78 }79 }80 catch(InstantiationException e)81 {82 //TODO Auto-generated catch block

83 e.printStackTrace();84 }85 catch(ClassNotFoundException e) {86 //TODO Auto-generated catch block

87 e.printStackTrace();88 } catch(IllegalAccessException e1) {89 //TODO Auto-generated catch block

90 e1.printStackTrace();91 }92 return null;93 }94

95 //查询数据库里面所有的元素

96 public static void Listsql(Connection conn) throwsSQLException97 {98 //Connection conn = MysqlConnection();//每次打开一次数据库效率较低

99 PreparedStatement pstmt;100 ResultSet rset;101 String sql = "select * from student";102 pstmt =conn.prepareStatement(sql);103 //获取结果

104 rset =pstmt.executeQuery();105 while(rset.next())106 {107 System.out.println(rset.getString("sno")+"\t"+rset.getString("sname")+"\t"+rset.getString("sex")); //Print col 1

108 }109 //conn.close();

110 }111

112 //向数据库里面插入元素

113 public static void Insertsql(Connection conn) throwsSQLException114 {115 //Connection conn = MysqlConnection();

116 PreparedStatement pstmt;117 String sql = "insert student(sno,sname,sex) values(?,?,?)";118 pstmt =conn.prepareStatement(sql);119 Scanner in = newScanner(System.in);120 System.out.println("输入你要插入的数据:(依次为: sno(学号),sname(姓名),sex(性别))");121 String name,no,se;122 no =in.next();123 name =in.next();124 se =in.next();125 pstmt.setString(1, no);126 pstmt.setString(2,name);127 pstmt.setString(3, se);128 //获得结果

129 pstmt.executeUpdate();130 System.out.println("Insert success.Your insert data is:");131 System.out.printf("no = %s, name = %s, se = %s\n",no,name,se);132 //conn.close();133 //in.close();

134 }135

136 //从数据库里面删除元素

137 public static void Deletesql(Connection conn) throwsSQLException138 {139 //Connection conn = MysqlConnection();

140 PreparedStatement pstmt;141 String sql = "delete from student where sname = ?";142 pstmt =conn.prepareStatement(sql);143 Scanner in =newScanner(System.in);144 System.out.println("输入你要删除人的姓名");145 String name;146 name =in.next();147 pstmt.setString(1,name);148 //获得结果

149 pstmt.executeUpdate();150 System.out.println("Delete success.Your Delete data is:");151 System.out.printf("name = %s\n",name);152 //conn.close();153 //in.close();

154 }155

156 //修改数据库里面的元素

157 public static void Updatesql(Connection conn) throwsSQLException158 {159 //Connection conn = MysqlConnection();

160 PreparedStatement pstmt;161 String sql = "update student set sex=? where sno=?";162 pstmt =conn.prepareStatement(sql);163 Scanner in = newScanner(System.in);164 String no,se;165 System.out.println("输入要修改的人的学号和对应的性别");166 no =in.next();167 se =in.next();168 pstmt.setString(1, se);169 pstmt.setString(2, no);170 //获得结果

171 pstmt.executeUpdate();172 System.out.println("Update success.Your Update data is:");173 System.out.printf("sno %s sex change to %s\n",no,se);174 //conn.close();175 //in.close();

176 }177 }