jdbc实战演练

好久没有发这个东西了,一小子跳这么快,哈哈哈哈哈.

不懂得jdbc是什么的小朋友们可以查一查,不懂得sql的小朋友也可以去查一查,本篇文章涉及到的新知识大概就这俩~

首先,关门,放出成果图!

 

 

 

 

 

 

 

 演示完毕,不会有人觉得我这些表数据是我自己修改的吧~不会吧不会吧

接下来放源代码出来,具体可以改,如果看不懂的可以评论问我,我可以稍微解释下.

import java.util.Scanner;

public class NewsMain {
    public void menu(){
        System.out.println("欢迎来到新闻管理系统");
        System.out.println("========================");
        System.out.println("       1-添加新闻");
        System.out.println("       2-查看新闻");
        System.out.println("       3-编辑新闻");
        System.out.println("       4-删除新闻");
        System.out.println("       5-退出系统");
        System.out.println("请输入1-5之间的数字进行操作:");
    }

    public static void main(String[] args){
        NewsMain nm = new NewsMain();
        DruidAndDbtils db = new DruidAndDbtils();
        Scanner in = new Scanner(System.in);
        boolean flag;
        while (true){
            flag = false;
            nm.menu();
            int n = 0;
            try {
                n = in.nextInt();
            } catch (Exception e) {
                e.printStackTrace();
                in.next();
                System.out.println("请输入标准数字!");
                continue;
            }
            if (n == 5){
                System.out.println("退出系统!");
                break;
            }
            switch(n){
                case 1:
                    System.out.println("添加新闻!");
                    db.addNews();
                    break;
                case 2:
                    System.out.println("查看新闻!");
                    db.researchNews();
                    break;
                case 3:
                    System.out.println("编辑新闻!");
                    db.reNews();
                    break;
                case 4:
                    System.out.println("删除新闻!");
                    db.deleteNews();
                    break;
                default : flag = true; break;
            }
            if (flag){
                System.out.println("输入错误,请重新输入1-5之间的数字:");
                continue;
            }
        }
    }
}

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

public class DruidAndDbtils {
    //查询新闻
    public void researchNews(){
        Scanner in = new Scanner(System.in);
        System.out.println("请输入要查看的新闻页数(一页十条):");
        int n = in.nextInt();
        Properties properties = new Properties();
        String propertyFile = DruidAndDbtils.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            QueryRunner qr = new QueryRunner(dataSource);
            List<News> list = qr.query("select * from new limit ?, 10", new BeanListHandler<>(News.class), new Object[]{(n - 1) * 10});
            for (News nw : list){
                System.out.println("asdas");
                System.out.println(nw.toString());
            }
            System.out.println("新闻查询完毕!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //添加数据
    public void addNews(){
        Connection conn = null;
        Scanner in = new Scanner(System.in);
        Scanner in0 = new Scanner(System.in);
        System.out.println("请输入新闻ID:");
        int id = in.nextInt();
        System.out.println("请输入新闻标题:");
        String title = in0.nextLine();
        System.out.println("请输入新闻内容:");
        String content = in0.nextLine();
        System.out.println("请输入新闻创建时间:");
        String time = in0.next();
        java.util.Date udTime = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            udTime = sdf.parse(time);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        long time0 = udTime.getTime();
        java.sql.Date sdTime = new java.sql.Date(time0);
        Properties properties = new Properties();
        String propertyFile = DruidAndDbtils.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            String sql = "insert into new(id, title, content, create_time) values(?, ?, ?, ?)";
            QueryRunner qr = new QueryRunner();
            qr.update(conn, sql, new Object[]{id, title, content, sdTime});
            System.out.println("新闻插入完毕!");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            close(conn);
        }
    }
    //编辑新闻
    public void reNews(){
        Connection conn = null;
        Scanner in = new Scanner(System.in);
        Scanner in0 = new Scanner(System.in);
        System.out.println("请输入新闻ID:");
        int id = in.nextInt();
        System.out.println("请输入新闻标题:");
        String title = in0.nextLine();
        System.out.println("请输入新闻内容:");
        String content = in0.nextLine();
        System.out.println("请输入新闻创建时间:");
        String time = in0.next();
        java.util.Date udTime = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            udTime = sdf.parse(time);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        long time0 = udTime.getTime();
        java.sql.Date sdTime = new java.sql.Date(time0);
        Properties properties = new Properties();
        String propertyFile = DruidAndDbtils.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            String sql = "update new set title = ?, content = ?, create_time = ? where id = ?";
            QueryRunner qr = new QueryRunner();
            qr.update(conn, sql, new Object[]{title, content, sdTime, id});
            System.out.println("新闻修改完毕");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            close(conn);
        }
    }
    //删除新闻
    public void deleteNews(){
        Connection conn = null;
        Scanner in = new Scanner(System.in);
        System.out.println("请输入想要删除的新闻的ID:");
        int id = in.nextInt();
        Properties properties = new Properties();
        try {
            String propertyFile = DruidAndDbtils.class.getResource("/druid-config.properties").getPath();
            propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            String sql = "delete from new where id = ?";
            QueryRunner qr = new QueryRunner();
            qr.update(conn, sql, new Object[]{id});
            System.out.println("新闻删除完毕!");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            close(conn);
        }
    }
    //关闭链接等
    public static void close(Connection conn){
        try {
            if (conn != null && !conn.isClosed()){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

import java.util.Date;

public class News {
    private int id;
    private String title;
    private String content;
    private Date create_time;

    public News(int id, String title, String content, Date create_time){
        this.setId(id);
        this.setTitle(title);
        this.setContent(content);
        this.setCreate_time(create_time);
    }
    public News(){}

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Date getCreate_time() {
        return create_time;
    }

    public void setCreate_time(Date create_time) {
        this.create_time = create_time;
    }

    @Override
    public String toString() {
        return "新闻:{" +
                "新闻ID:" + id +
                ", 新闻标题:'" + title + '\'' +
                ", 新闻内容:'" + content + '\'' +
                ", 新闻创建时间:" + create_time +
                '}';
    }


}

看第二段代码就知道,我使用了DRUID连接池以及APACHE的DBUTILS

所以叫DruidAndDbUtils(代码里面我少写一个U,不过我真的懒得改了,原谅我吧!)

还记得第一次链接数据库时候那激动的心情

\不多说了,加油继续干!