你的位置:首页 > 数据库

[数据库]Mysql数据库的增删改查


在运行程序之前需要在mysql数据库中创建test数据库,如下图所示:

下面是具体是实现程序:

  

 1 package News; 2  3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.Statement; 7 import java.util.Scanner; 8  9 public class News { 10   public static void main(String[] args) { 11     System.out.println("< < < < < < 使用ArrayList实现新闻管理系统 > > > > > >"); 12  13     while (true) { 14       Scanner scanner = new Scanner(System.in); 15       System.out 16           .println("1. 查询全部新闻\n2. 添加新闻\n3. 删除新闻\n4. 按标题查询新闻\n5. 按标题修改新闻 \n0.退出\n请选择功能(1,2,3,4,5,0):"); 17       int selected = scanner.nextInt(); 18  19       switch (selected) { 20       case 1: 21         reads(); 22         break; 23  24       case 2: 25         System.out.println("请输入新闻标题:"); 26         String title = scanner.next(); 27         System.out.println("请输入新闻内容:"); 28         String content = scanner.next(); 29         System.out.println("请输入新闻备注:"); 30         String remark = scanner.next(); 31  32         add(title, content,remark); 33         break; 34  35       case 3: 36         System.out.println("请输入新闻标题:"); 37         String title2 = scanner.next(); 38         delete(title2); 39         break; 40  41       case 4: 42         System.out.println("请输入新闻标题:"); 43         String title1 = scanner.next(); 44         read(title1); 45         break; 46  47       case 5: 48         System.out.println("请输入新闻标题:"); 49         String title3 = scanner.next(); 50         update(title3); 51         break; 52  53       case 0: 54         scanner.close(); 55         System.exit(0); 56         break; 57  58       default: 59         System.out.println("输入错误,请重新输入:"); 60         break; 61       } 62     } 63   } 64  65   /** 66    * 定义添加新闻方法 67    *  68    * @param title 69    *      标题 70    * @param content 71    *      内容 72   */ 73   public static void add(String title, String content,String remark) { 74     try { 75       Class.forName("com.mysql.jdbc.Driver"); 76       String url = "jdbc:mysql://localhost:3306/test"; 77       String user = "root"; 78       String password = "root"; 79       Connection conn = DriverManager.getConnection(url, user, password); 80       Statement st = conn.createStatement(); 81       String sql = "insert into news values(null,'" + title + "','" 82           + content + "','"+ remark + "')"; 83       int row = st.executeUpdate(sql); 84       System.out.println(row > 0 ? "添加成功" : "添加失败"); 85     } catch (Exception e) { 86       e.printStackTrace(); 87     } 88   } 89  90   /** 91    * 定义查询全部方法 92   */ 93   public static void reads() { 94     try { 95       Class.forName("com.mysql.jdbc.Driver"); 96       String url = "jdbc:mysql://localhost:3306/test"; 97       String user = "root"; 98       String password = "root"; 99       Connection conn = DriverManager.getConnection(url, user, password);100       Statement st = conn.createStatement();101       String sql = "select * from news";102       ResultSet list = st.executeQuery(sql);103       if (list != null) {104         while (list.next()) {105           int id = list.getInt(1);106           String title = list.getString(2);107           String content = list.getString(3);108           String remark = list.getString(4);109           System.out.println(id + "\t" + title + "\t" + content110               + "\t" + remark);111         }112       }113     } catch (Exception e) {114       e.printStackTrace();115     }116   }117 118   /**119    * 定义根据标题查询新闻的方法120    * 121    * @param title1122    *      新闻标题123   */124   public static void read(String title1) {125     try {126       Class.forName("com.mysql.jdbc.Driver");127       String url = "jdbc:mysql://localhost/test";128       String user = "root";129       String password = "root";130       Connection conn = DriverManager.getConnection(url, user, password);131       Statement st = conn.createStatement();132       String sql = "select * from news where title='" + title1 + "'";133       ResultSet row = st.executeQuery(sql);134       if (row.next()) {135         int id = row.getInt(1);136         String title = row.getString(2);137         String content = row.getString(3);138         String remark = row.getString(4);139         System.out.println(id + "\t" + title + "\t" + content + "\t"140             + remark);141       }142     } catch (Exception e) {143       e.printStackTrace();144     }145   }146 147   /**148    * 定义按标题删除新闻的方法149    * 150    * @param title151    *      新闻方法152   */153   public static void delete(String title) {154     try {155       Class.forName("com.mysql.jdbc.Driver");156       String url = "jdbc:mysql://localhost:3306/test";157       String user = "root";158       String password = "root";159       Connection conn = DriverManager.getConnection(url, user, password);160       Statement st = conn.createStatement();161       String sql = "delete from news where title='" + title + "'";162       int row = st.executeUpdate(sql);163       System.out.println(row > 0 ? "删除成功" : "删除失败");164     } catch (Exception e) {165       e.printStackTrace();166     }167   }168 169   /**170    * 定义根据标题修改新闻的方法171    * 172    * @param title173    *      新闻标题174   */175   public static void update(String title) {176 177     try {178       Class.forName("com.mysql.jdbc.Driver");179       String url = "jdbc:mysql://localhost:3306/test";180       String user = "root";181       String password = "root";182       Connection conn = DriverManager.getConnection(url, user, password);183       Statement st = conn.createStatement();184       String sql = "select * from news where title='" + title + "'";185       ResultSet list = st.executeQuery(sql);186       if (list.next()) {187         Scanner sca = new Scanner(System.in);188         System.out.println("请输入新的新闻标题:");189         String title4 = sca.next();190         System.out.println("请输入新闻内容:");191         String content = sca.next();192         System.out.println("请输入备注:");193         String remark = sca.next();194         195         String sql1 = "update news set title='" + title4196             + "',content='" + content + "',remark='" + remark + "' where title='"+title+"'";197         System.out.println(sql1);198         int u = st.executeUpdate(sql1);199         System.out.println(u > 0 ? "修改成功" : "修改失败");200         sca.close();201       }202     } catch (Exception e) {203       e.printStackTrace();204     }205   }206 }