1 //import java.io.*;
  2 import java.util.*;
  3 import java.sql.*;
  4 
  5 /**
  6  * 
  7  * @date 2014-05-20
  8  * @author asif
  9  * @作用  java实现对mysql数据库的增减删查
 10  * @bug 插入，删除，更新的时候异常停止
 11  *
 12  */
 13 
 14 public class ZGSC {
 15     public static void main(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 = new Scanner(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 static Connection MysqlConnection()
 62     {
 63         String username = "root";
 64         String userpasswd = "a0606a";
 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                 return contemp;
 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) throws SQLException
 97     {
 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) throws SQLException
114     {
115         //Connection conn = MysqlConnection();
116         PreparedStatement pstmt;
117         String sql = "insert student(sno,sname,sex) values(?,?,?)";
118         pstmt = conn.prepareStatement(sql);
119         Scanner in = new Scanner(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) throws SQLException
138     {
139         //Connection conn = MysqlConnection();
140         PreparedStatement pstmt;
141         String sql = "delete from student where sname = ?";
142         pstmt = conn.prepareStatement(sql);
143         Scanner in =new Scanner(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) throws SQLException
158     {
159         //Connection conn = MysqlConnection();
160         PreparedStatement pstmt;
161         String sql = "update student set sex=? where sno=?";
162         pstmt = conn.prepareStatement(sql);
163         Scanner in = new Scanner(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 }