美文网首页
java数据库管理之jdbc

java数据库管理之jdbc

作者: 唐英钏 | 来源:发表于2018-03-19 13:18 被阅读0次

    JDBC的开发步骤

    public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    // write your code here
        
        //1.加载mysql数据库驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获得数据库连接对象
        String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url,username,password);
        //System.out.println(connection);

        //3.创建数据库执行对象，相当于navicat里的查询窗口
        Statement statement = connection.createStatement();

        //4.执行查询语句
        ResultSet resultSet = statement.executeQuery("select * from sort");
        while(resultSet.next())//等同于while(resultSet.next() = true)
        {
            //System.out.println(resultSet.getString("sname"));//resultSet.getX("Y")Y属于什么类型，X就是什么类型
            String strName = resultSet.getString("sname");
            Double dPrice = resultSet.getDouble("sprice");
            System.out.println(strName + "  " + dPrice);
        }

        //插入一条记录
        //int rows = statement.executeUpdate("insert into sort(sname,sprice,sdesc) values('汽车用品',50000,'疯狂涨价')");

        //删除一条记录
        //int delRows = statement.executeUpdate("delete from sort where sid = 1");

        //5.关闭数据库连接
        resultSet.close();//需要先关闭
        statement.close();
        connection.close();
    }
}

    注入攻击(不用这个代码编程序)

    public class LoginDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载mysql数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库连接对象
        String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url,username,password);

        //3.创建数据库执行对象，相当于navicat里的查询窗口
        Statement statement = connection.createStatement();
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String uName = scanner.nextLine();
        System.out.println("请输入密码：");
        String pwd = scanner.nextLine();
        String strSql = "select * from users where username = '"+uName+"' and password = '"+pwd+"' ";

        //4.处理登录
        ResultSet resultSet = statement.executeQuery(strSql);
        if (resultSet.next() == false)
        {
            System.out.println("登录失败");
        }
        else
        {
            System.out.println("登录成功");
        }

        //5.关闭连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

    (用这个代码编程序)

    public class LoginDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载mysql数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库连接对象
        String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url,username,password);

        //3.创建数据库执行对象，相当于navicat里的查询窗口
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String uName = scanner.nextLine();
        System.out.println("请输入密码：");
        String pwd = scanner.nextLine();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from users where username = ? and password = ?");
        preparedStatement.setString(1,uName);
        preparedStatement.setString(2,pwd);

        //4.处理登录
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next() == false)
        {
            System.out.println("登录失败");
        }
        else
        {
            System.out.println("登录成功");
        }

        //5.关闭连接
        resultSet.close();
        preparedStatement.close();
        connection.close();

    }
}

    更新操作

    public class UpdateDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载mysql数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库连接对象
        String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url,username,password);
        //3.创建数据库执行对象，相当于navicat里的查询窗口
        PreparedStatement preparedStatement = connection.prepareStatement("update sort set sname = ? ,sprice = ?, sdesc=? where sid = ? ");
        preparedStatement.setString(1,"口红");
        preparedStatement.setDouble(2,11000.1);
        preparedStatement.setString(3,"限量版口红");
        preparedStatement.setInt(4,4);
        //4.处理(执行)
        int rows = preparedStatement.executeUpdate();
        //5.关闭数据库连接
        preparedStatement.close();
        connection.close();
    }
}

    JDBC的工具类(代码固定)

    public class JDBCUtils {
     private static Connection conn = null;
        private static String url;
        private static String username;
        private static String password;
        static {
            InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            try {
                properties.load(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
            //1.加载mysql数据库驱动
                try {
                    Class.forName(properties.getProperty("driver"));
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }

            //2.获得数据库连接对象
             url = properties.getProperty("url");
            //要连接的数据库服务器的基本信息，包括（ip，端口，数据库名）
             username = properties.getProperty("username");
             password = properties.getProperty("password");


        }

        private JDBCUtils(){}


        public static Connection getConnection()
        {
            try {
                conn = DriverManager.getConnection(url,username,password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }

        public static void close(Statement statement, Connection connection)
        {
            if(statement != null)
            {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(connection != null)
            {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

        public static void close(ResultSet resultSet, Statement statement, Connection connection)
        {
            if(resultSet != null)
            {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(statement != null)
            {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(connection != null)
            {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
}

    数据表数据存储对象

    public class Sort {
    private int sid;
    private String sname;
    private double sprice;
    private String sdesc;
    private Date sdate;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public double getSprice() {
        return sprice;
    }

    public void setSprice(double sprice) {
        this.sprice = sprice;
    }

    public String getSdesc() {
        return sdesc;
    }

    public void setSdesc(String sdesc) {
        this.sdesc = sdesc;
    }

    public Date getSdate() {
        return sdate;
    }

    public void setSdate(Date sdate) {
        this.sdate = sdate;
    }

    @Override
    public String toString() {
        return "Sort{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", sprice=" + sprice +
                ", sdesc='" + sdesc + '\'' +
                ", sdate=" + sdate +
                '}';
    }
}



     //增加
    public static int AddSort(Sort sort) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into sort(sname,sprice,sdesc,sdate) values(?,?,?,?)");
        preparedStatement.setString(1,sort.getSname());
        preparedStatement.setDouble(2,sort.getSprice());
        preparedStatement.setString(3,sort.getSdesc());

        //java.util.date--->java.sql.date
        Date date = sort.getSdate();
        //得到date对应的时间戳
        long timestamp = date.getTime();
        //通过时间戳创建java.sql.Date类型的对象
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
        preparedStatement.setTimestamp(4,sqlDate);

        int row = preparedStatement.executeUpdate();
        JDBCUtils.close(preparedStatement,connection);
        return row;
//增加
    public static void main(String[] args) throws SQLException {
        System.out.println("增加");
        Sort sort = new Sort();
        sort.setSname("小饰品");
        sort.setSprice(900);
        sort.setSdesc("好看");
        Date date = new Date();
        sort.setSdate(date);
        AddSort(sort);
    }

    //删除
    public static int deleteSortByID(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("delete from sort where sid = ?");
        preparedStatement.setInt(1,id);
        int row = preparedStatement.executeUpdate();
        JDBCUtils.close(preparedStatement,connection);
        return row;
    }
//删除
public static void main(String[] args) throws SQLException {
        System.out.println("删除");
        deleteSortByID(4);
    }

    //更新(改)
    public static int updateSortByID(Sort sort) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("update sort set sname = ?,sprice = ?, sdesc=?,sdate = ? where sid = ?");
        preparedStatement.setString(1,sort.getSname());
        preparedStatement.setDouble(2,sort.getSprice());
        preparedStatement.setString(3,sort.getSdesc());
        Date date = sort.getSdate();
        //得到date对应的时间戳
        long timestamp = date.getTime();
        //通过时间戳创建java.sql.Date类型的对象
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
        preparedStatement.setTimestamp(4,sqlDate);
        preparedStatement.setInt(5,sort.getSid());
        int row = preparedStatement.executeUpdate();
        JDBCUtils.close(preparedStatement,connection);
        return row;
    }
//更新
public static void main(String[] args) throws SQLException {
        System.out.println("更新");
        Sort sort = new Sort();
        sort.setSname("芭比娃娃");
        sort.setSprice(1400);
        sort.setSdesc("好看");
        Date date = new Date();
        sort.setSdate(date);
        sort.setSid(9);
        updateSortByID(sort);
     }

    //查询——查一个
    public  static Sort getSortByID(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from sort where sid = ?");
        preparedStatement.setInt(1,id);

        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next() == true)
        {
            int sid = resultSet.getInt("sid");
            String strName = resultSet.getString("sname");
            double dPrice = resultSet.getDouble("sprice");
            String sdesc = resultSet.getString("sdesc");

            Sort sort = new Sort();
            sort.setSid(sid);
            sort.setSname(strName);
            sort.setSprice(dPrice);
            sort.setSdesc(sdesc);
            return sort;
        }
        else
        {
            return null;
        }
    }
    //查询——查全部
    public static List<Sort> getAllSort() throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from sort");

        List<Sort> sortList = new ArrayList<>();

        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next() == true)
        {
            int sid = resultSet.getInt("sid");
            String strName = resultSet.getString("sname");
            double dPrice = resultSet.getDouble("sprice");
            String sdesc = resultSet.getString("sdesc");
            Date date = resultSet.getTimestamp("sdate");

            Sort sort = new Sort();
            sort.setSid(sid);
            sort.setSname(strName);
            sort.setSprice(dPrice);
            sort.setSdesc(sdesc);
            sort.setSdate(date);
            sortList.add(sort);
        }
        return sortList;
    }

//查询
    public static void main(String[] args) throws SQLException {
        //查一个
        System.out.println("查一个");
        Sort sort = getSortByID(2);
        if(sort != null)
        {
            System.out.println(sort);
        }
        else
        {
            System.out.println("不存在");
        }
        //查全部
        System.out.println("查全部");
        List<Sort> sortList = getAllSort();
        for (Sort sort1: sortList)
        {
            System.out.println(sort1);
        }

    db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=UTF-8
username=root
password=root

    java.util.date转为java.sql.date

    Date date = sort.getSdate();
//得到date对应时间戳
long timestamp = date.getTime();
//通过时间戳创建java.sql.Date类型的对象
java.sql.Date  sqiDate = new java.sql.Date(timestamp);

    相关文章

      网友评论

          本文标题：java数据库管理之jdbc

          本文链接：https://www.haomeiwen.com/subject/nnflaxtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|java数据库管理之jdbc|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！