JAVA萌新学习day19

MyJDBC

import com.qf.java1904.day13.StudyDemo;
import com.qf.java1904.day17.Course;
import com.qf.java1904.day17.Student;

import java.sql.*;
import java.util.ArrayList;

/**
 * 1.设计用户表(类)
 *
 * 字段：主键id 用户名name,密码password 权限level(管理员或普通用户)
 *
 * 2.设计商品表(类)
 *
 * 字段：主键id 商品名称name,商品价格price,商品一级分类typea(参考京东)，商品二级分类typeb，商品说明info
 *
 * 3.设计管理类 StoreManager
 * 功能
 * 1.创建两张表
 * 2.插入一些随机数据(程序插入也可用.sql文件导入)
 * 3.程序启动，出现提示菜单：1.用户注册 .2用户登录.3.商品管理
 * 4.如果用户选择3.进入下一级：
 * 提示：1.添加商品 2.显示所有商品 3.删除商品（提示用户输入商品名称）4.查询商品（提示用户输入根据哪个条件删除）
 *
 */

public class MyJDBC {

    // mysql -h127.0.0.1 -uroot -p

    //连接数据库
    public static void main(String[] args) {
        //DataManager.sqlTest();
        //jdbc();

        SelectStudent ss = new SelectStudent();
        ss.connection();

        String sql ="select id ,name,major form student limit ?,?";
        ArrayList<Student>  list =ss.selectFromTable(sql, new Student(), 1,10);

        for(Student s : list){
            System.out.println(s);
        }


    }
    //没有封装前的jdbc使用
    public static void jdbc(){
        //1.注册驱动
        //声明连接对象
        Connection connection = null;
        //声明 Statement对象
        Statement statement = null;
        //声明 结果集（ResultSet）对象
        ResultSet resultSet = null;

        try{
            Class.forName("com.mysql.jdbc.Driver");
            //2.连接数据库
            // URL-----
            //连接的主机地址和数据库的名字
            String url = "jdbc:mysql://localhost:3306?useSSL=true&characterEncoding=utf8";
            //登录的用户名
            String name = "root";
            //登录的密码
            String password = "100527";
            //连接数据库
            connection = DriverManager.getConnection(url, name, password);
            if(connection != null){
                System.out.println("MyJDBC.main:连接成功");
                //获得操作sql语句的对象
                statement = connection.createStatement();
                //执行sql语句 修改数据
                int state = statement.executeUpdate("update student set age = 36 where id = 10");
                System.out.println("state:" + state);
                //删除数据
                state = statement.executeUpdate("delete from course where name = '语文'");
                System.out.println("state:" + state);
                //插入数据

                // state = statement.executeUpdate("insert into course(name,major,score) values ('语文','文学',8)");
                // System.out.println("state:" + state);

               /*
               +----+--------+----------+-------+
| id | name   | major    | score |
+----+--------+----------+-------+
|  1 | 数学   | Math     |     5 |
|  2 | 英语   | English  |     7 |
|  3 | Java   | Math     |     3 |
|  4 | PHP    | English  |     7 |
|  5 | C++    | Math     |     1 |
|  6 | 政治   | Sport    |     7 |
|  7 | 历史   | Sport    |     5 |
|  8 | 数据库 | Sport    |     3 |
|  9 | 毛概   | Computer |     5 |
| 10 | 语文   | 文学     |     8 |
+----+--------+----------+-------+
                */
                //查询数据
                resultSet = statement.executeQuery("select id,name,major,score from course");
                //遍历结果集
                while (resultSet.next()){//类似迭代器,一次循环获得一条记录
                    //获得当前这条记录的内容
                    //根据列名（字段名）获得字段的值
                    //获得课程名
                    String courseName = resultSet.getString("name");
                    // courseName =resultSet.getString(1);
                    System.out.println("name:"+ courseName) ;
                    //获得专业名
                    String major = resultSet.getString("major");
                    System.out.println("name:"+ major) ;
                    //获得学分
                    int score = resultSet.getInt("score");
                    System.out.println("score:" + score);
                    //从结果集中获得当前记录的不同字段的值，需要根据字段类型，调用结果集对象的不同的get方法
                }

                String courseName = StudyDemo.randomString(8);

                Course c = new Course(courseName, "English", 10, 0);
                //state = statement.executeUpdate("insert into course(name,major,score) values (?,?,?)");
                //创建预处理对象
                PreparedStatement ps = connection.prepareStatement("insert into course(name,major,score) values (?,?,?)");
                //绑定每一个?的值(也就是每一个字段的值),需要根据字段的类型来调用相应的set方法
                //设置方法的第一个参数是字段的索引，从1开始
                ps.setString(1, c.getName());
                ps.setString(2,c.getMajor());
                ps.setInt(3, c.getScore());
                // Object[] o;
                //ps.setObject(i+1,o[i]);
                //执行sql语句
                int resultState = ps.executeUpdate();
                System.out.println(resultState);
                // String a="ffff";
                //System.out.printf("%d,f%,%s",1,10.0,a);

                // System.out.println("state:" + state);

            }
            else{
                System.out.println("MyJDBC.main:连接失败");
            }


        }
        catch (Exception e){
            System.out.println("MyJDBC.main:" + e.getMessage());
        }
        finally {
            //如果当前结果集对象不为空
            if(resultSet!=null){
                try{
                    //关闭结果集，释放资源
                    resultSet.close();
                }
                catch (Exception e){
                    System.out.println("MyJDBC.main"+ e.getMessage());
                }
            }
            //如果statement对象不为空
            if(statement!=null){
                try{
                    //关闭statement，释放资源
                    statement.close();
                }
                catch (Exception e){
                    System.out.println("MyJDBC.main"+ e.getMessage());
                }
            }
            //如果connection对象不为空
            if(connection!=null){
                try{
                    //关闭连接对象，释放资源
                    connection.close();
                }
                catch (Exception e){
                    System.out.println("MyJDBC.main"+ e.getMessage());
                }
            }

        }
    }

}

QFDatabase

//设计一个用来方便操作数据库的类

import java.sql.*;
import java.util.ArrayList;

//声明抽象类 并包含泛型参数
public abstract class QFDatabase<E> {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/java1904?useSSL=true&characterEncoding=utf8";
    private static String userName = "root";
    private static String password = "12356";
    //声明连接类的变量(引用)
    private Connection connection;
    //注册驱动，只执行一次，所以我们选择使用静态代码块
    static {
        try {
            //注册驱动
            Class.forName(driver);
        }
        catch (ClassNotFoundException e){
            System.out.println(e.getMessage());
        }
    }
    public void connection(){
        try{
          connection=DriverManager.getConnection(url, userName, password);
          System.out.println(connection!=null?"连接成功":"连接失败");
        }
        catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    //关闭资源
    public static void close(Connection c, Statement s, ResultSet r){
        try{
            if(r!=null){
                r.close();
            }
            if(s!=null){
                s.close();
            }
            if(c!=null){
                c.close();
            }
        }
        catch (SQLException e){
            System.out.println(e.getMessage());
        }
        finally {

        }
    }
    //绑定预处理的参数
    private void setParams(PreparedStatement ps,Object... params){
        try{
            int i=1;
            for(Object o : params){
                ps.setObject(i++, o);
            }
        }
        catch (SQLException e){
            System.out.println("QFDatabase.setParams" + e.getMessage());
        }
    }

    //用来执行sql语句,声明包含一个可变参的方法,可变参数好比数组
    public int executeUpdate(String sql,Object... params){

        PreparedStatement ps = null;
        try{
            if(connection!=null){
                ps = connection.prepareStatement(sql);
                setParams(ps, params);
                int resultState = ps.executeUpdate();
                return resultState;
            }
        }
        catch (SQLException e){
            System.out.println(e.getMessage());
        }
        finally {
            //关闭资源
            close(null, ps, null);
        }
        return -1;
    }
    public boolean isExists(String sql,Object... params){

        PreparedStatement ps = null;
        try{
            if(connection!=null){
                ps = connection.prepareStatement(sql);
                setParams(ps, params);
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    return true;
                }
            }
        }
        catch (SQLException e){
            System.out.println(e.getMessage());
        }
        finally {
            //关闭资源
            close(null, ps, null);
        }
        return false;
    }
    //解析结果集
    public abstract void parseResultSet(E element,ResultSet rs,ArrayList<E> list);

    public ArrayList<E> selectFromTable(String sql,E element,Object... params){

        PreparedStatement ps = null;
        try{
            if(connection!=null){
                //创建用来保存结果的ArrayList对象
                ArrayList<E> list = new ArrayList<>();
                //获得PreparedStatement对象
                ps = connection.prepareStatement(sql);
                //绑定参数值(也就是sql字符串中的?的值)
                setParams(ps, params);
                //执行查询
                ResultSet rs = ps.executeQuery();
                //解析结果集，此方法为抽象方法，需要子类实现
                parseResultSet(element, rs, list);
                return list;
            }
        }
        catch (SQLException e){
            System.out.println(e.getMessage());
        }
        return null;
    }

}

QFDatabase

import Student;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class SelectStudent extends QFDatabase<Student>{
    @Override
    public void parseResultSet(Student element, ResultSet rs, ArrayList<Student> list) {
        try{
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String major = rs.getString("major");

                Student s = new Student();
                s.setId(id);
                s.setName(name);
                s.setMajor(major);

                list.add(s);

            }
        }
        catch (SQLException e){

        }
    }
}

DataManager

public class DataManager {

    public static void sqlTest(){

        //可以执行 增 删 改 和表结构相关的一些sql
        String sql = "delete from course where id = ? ";
        sql = "delete from course where id = ? or id =? ";
        sql = "delete from course where id in(?,?) ";
        //把sql字符串中的？号换成实际需要的值，必面是合法的sql语句
        sql = "update course set score = ? where id=?";
        //sql = "CREATE TABLE t1 ( id int,name varchar(100)) charset=utf8";
       // int resultState= QFDatabase.executeUpdate(sql);
        int resultState=0;

        sql = "select name from t1 where ?=?";
        boolean isExists = QFDatabase.isExists(sql,"name","小明");
       if(isExists){
           System.out.println("存在");
       }
       else{
           System.out.println("不存在");
           sql = "insert into t1 (id,name) values (?,?)";
           resultState = QFDatabase.executeUpdate(sql,10,"小明");
           System.out.println(resultState);
       }

    }

}

没写完没写完。时间不够没写完！！！！！！！！！！！！！以后在写