Mysql数据库的增删改查

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

下面是具体是实现程序:

　　

package News;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class News {
    public static void main(String[] args) {
        System.out.println("< < < < < < 使用ArrayList实现新闻管理系统 > > > > > >");

        while (true) {
            Scanner scanner = new Scanner(System.in);
            System.out
                    .println("1. 查询全部新闻
2. 添加新闻
3. 删除新闻
4. 按标题查询新闻
5. 按标题修改新闻 
0.退出
请选择功能（1，2，3，4，5，0）：");
            int selected = scanner.nextInt();

            switch (selected) {
            case 1:
                reads();
                break;

            case 2:
                System.out.println("请输入新闻标题:");
                String title = scanner.next();
                System.out.println("请输入新闻内容:");
                String content = scanner.next();
                System.out.println("请输入新闻备注:");
                String remark = scanner.next();

                add(title, content,remark);
                break;

            case 3:
                System.out.println("请输入新闻标题:");
                String title2 = scanner.next();
                delete(title2);
                break;

            case 4:
                System.out.println("请输入新闻标题:");
                String title1 = scanner.next();
                read(title1);
                break;

            case 5:
                System.out.println("请输入新闻标题:");
                String title3 = scanner.next();
                update(title3);
                break;

            case 0:
                scanner.close();
                System.exit(0);
                break;

            default:
                System.out.println("输入错误，请重新输入:");
                break;
            }
        }
    }

    /**
     * 定义添加新闻方法
     * 
     * @param title
     *            标题
     * @param content
     *            内容
     */
    public static void add(String title, String content,String remark) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement st = conn.createStatement();
            String sql = "insert into news values(null,'" + title + "','"
                    + content + "','"+ remark + "')";
            int row = st.executeUpdate(sql);
            System.out.println(row > 0 ? "添加成功" : "添加失败");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 定义查询全部方法
     */
    public static void reads() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement st = conn.createStatement();
            String sql = "select * from news";
            ResultSet list = st.executeQuery(sql);
            if (list != null) {
                while (list.next()) {
                    int id = list.getInt(1);
                    String title = list.getString(2);
                    String content = list.getString(3);
                    String remark = list.getString(4);
                    System.out.println(id + "	" + title + "	" + content
                            + "	" + remark);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 定义根据标题查询新闻的方法
     * 
     * @param title1
     *            新闻标题
     */
    public static void read(String title1) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/test";
            String user = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement st = conn.createStatement();
            String sql = "select * from news where title='" + title1 + "'";
            ResultSet row = st.executeQuery(sql);
            if (row.next()) {
                int id = row.getInt(1);
                String title = row.getString(2);
                String content = row.getString(3);
                String remark = row.getString(4);
                System.out.println(id + "	" + title + "	" + content + "	"
                        + remark);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 定义按标题删除新闻的方法
     * 
     * @param title
     *            新闻方法
     */
    public static void delete(String title) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement st = conn.createStatement();
            String sql = "delete from news where title='" + title + "'";
            int row = st.executeUpdate(sql);
            System.out.println(row > 0 ? "删除成功" : "删除失败");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 定义根据标题修改新闻的方法
     * 
     * @param title
     *            新闻标题
     */
    public static void update(String title) {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement st = conn.createStatement();
            String sql = "select * from news where title='" + title + "'";
            ResultSet list = st.executeQuery(sql);
            if (list.next()) {
                Scanner sca = new Scanner(System.in);
                System.out.println("请输入新的新闻标题:");
                String title4 = sca.next();
                System.out.println("请输入新闻内容:");
                String content = sca.next();
                System.out.println("请输入备注:");
                String remark = sca.next();
                
                String sql1 = "update news set title='" + title4
                        + "',content='" + content + "',remark='" + remark + "' where title='"+title+"'";
                System.out.println(sql1);
                int u = st.executeUpdate(sql1);
                System.out.println(u > 0 ? "修改成功" : "修改失败");
                sca.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}