关于数据库连接方式jdbc的一些总结

最新推荐文章于 2024-06-16 04:56:11 发布

不会编码啊

最新推荐文章于 2024-06-16 04:56:11 发布

阅读量49

点赞数 1

文章标签：数据库 android

本文链接：https://blog.csdn.net/qq_74047465/article/details/132550331

版权

1.jdbc的实现步骤

1.引入驱动包 =》2.加载驱动 =》3.建立连接 =》4.创建执行SQL的对象 =》5.执行sql语句，等待服务器反应结果 =》6.释放资源

public class jdbcStep {
    public static void main(String[] args) {
        
        //1.引入驱动包
        
        //2.加载驱动，mysql8.0不需要手动加载
//        try {
//            //利用Driver里面的静态代码块来注册驱动
//            Class.forName("com.mysql.cj.jdbc.Driver");
//       //mysql8.0以下的版本使用该驱动
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
        
        try {
            //手动注册驱动
//            DriverManager.registerDriver(new Driver());
            
            //3.使用驱动管理器来获取连接
            
            //我们使用的是jdbc技术来连接，需要以jdbc开头
            //然后接数据库的协议，例如mysql://数据库的地址/数据库名称
            //mysql8.0需要加上时区
            String url = "jdbc:mysql://localhost:3306/lesson?severTimezone=Asia/shanghai";
            
            Connection connection = DriverManager.getConnection(url, "root", "root");
            
            //4.创建执行SQL的对象
            Statement statement = connection.createStatement();
            
            //5.使用SQL执行对象向服务器发送指令，并等待服务器返回的结果
            ResultSet resultSet = statement.executeQuery("select * from hero");
            
            while(resultSet.next()){//结果集是个临时表，光标默认在0的位置，创建循环对接数据
                //向后移动一次就会换行
                
                int number = resultSet.getInt(1);
                
                String name = resultSet.getString("name");
                
                String skill = resultSet.getString("skill");
                
                double score = resultSet.getDouble("score");
                
                int n_no = resultSet.getInt(5);
                
            System.out.println(number + "," + name + "," + skill + "," + score + ","+ n_no);
            }
            
            connection.close();
            
            statement.close();

        } catch (SQLException e) {
            
            e.printStackTrace();

        }
    }
}

2.通过反射来获取数据库中的数据

public class jdbcDemo {
    
    private static String URL = "jdbc:mysql://localhost:3306/lesson?severTimezone=Asia/shanghai";

    private static String USER = "root";

    private static String PASSWORD = "root";

    public static void main(String[] args) {


//    public static void query(){

            try {

                Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

                Statement statement = connection.createStatement();

                ResultSet resultSet = statement.executeQuery("select number,name,skill,score,n_no worldNumber from hero");

                Class<Student> clazz = Student.class;

                while(resultSet.next()){

                    //ORM - Object Relation Mapping 对象关系映射

//                    Integer number = resultSet.getObject(1, Integer.class);

//                    String name = resultSet.getObject("name", String.class);

//                    String skill = resultSet.getObject("skill", String.class);

//                    Double  score = resultSet.getObject("score", Double.class);

                  int n_no = resultSet.getInt(5);

//                    Integer worldNumber = resultSet.getObject(5, Integer.class);

//                    Student student = new Student();

//                    student.setNumber(number);

//                    student.setName(name);

//                    student.setSkill(skill);

//                    student.setScore(score);

//                    student.setWorldNumber(worldNumber);

                    Student student =  clazz.newInstance();//利用Class类创建一个实例化对象

                    Field[] fields = clazz.getDeclaredFields();

                    for (Field field : fields) {

                        String fieldName = field.getName();

                        Class<?> fieldType = field.getType();

                        String methodName = "set" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);

                        Method method = clazz.getDeclaredMethod(methodName,fieldType);

                        Object value = resultSet.getObject(fieldName, fieldType);

                        method.invoke(student,value);

                    }

                    System.out.println(student);

                }

                resultSet.close();

                connection.close();

                statement.close();

            } catch (Exception e) {

                e.printStackTrace();

            }
//        }
 }
}

3.避免sql注入问题

sql注入问题：在查询输入条件时，如果条件输入了一个true，那么会返回所有数据的值

为了避免这一问题，我们往往使用sql预处理来解决(PreparedStatement)

public class jdbcDemo {

    private static String URL = "jdbc:mysql://localhost:3306/lesson?
severTimezone=Asia/shanghai";

    private static String USER = "root";

    private static String PASSWORD = "root";

    public static void main(String[] args) {

        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入查询条件:");

        String s = scanner.nextLine();

//    public static void query(){

            try {

                Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                //创建一个预处理的对象，它能有效避免sql注入问题，注意和statement不同的是，在创建时就需要给定sql

                //并且预处理时sql中参数用？来占位，有几个？就有几个参数
                PreparedStatement ps = connection.prepareStatement("select number,name,skill,score,n_no worldNumber from hero where name = ? and score > ?");

                //设置参数的值，顺序是按照？的排列顺序来觉得，从1开始
                ps.setString(1,s);

                ps.setDouble(2,85.00);//第二个条件

                ResultSet resultSet = ps.executeQuery();

//                Statement statement = connection.createStatement();

//                ResultSet resultSet = statement.executeQuery("select 

number,name,skill,score,n_no worldNumber from hero where name = '" + s + "'");

                //存在sql注入问题，如果输入一个表达式，表达式为真，那么就会显示所有列表

                Class<Student> clazz = Student.class;

                while(resultSet.next()){

                    //ORM - Object Relation Mapping 对象关系映射

                    Student student =  clazz.newInstance();//利用Class类创建一个实例化对象

                    Field[] fields = clazz.getDeclaredFields();

                    for (Field field : fields) {

                        String fieldName = field.getName();

                        Class<?> fieldType = field.getType();

                        String methodName = "set" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);

                        Method method = clazz.getDeclaredMethod(methodName,fieldType);

                        Object value = resultSet.getObject(fieldName, fieldType);

                        method.invoke(student,value);

                    }

                    System.out.println(student);

                }

                resultSet.close();

                connection.close();

                ps.close();

            } catch (Exception e) {

                e.printStackTrace();

            }
//        }
 }
}

4.万能查询的构建

    public static void main(String... args) {

//        //万能查询1--实例

        String sql = "select name,skill,score,n_no worldNumber from hero where name like ? and score > ?";

//        String sql = "select name,skill,score,n_no worldNumber from hero";

        Object[] pramas = {"%然",80.0};//不清楚参数类型就用Object

        String p = null;

//        Class<Student> clazz = Student.class;

//        List<Student> students = query(sql, clazz);

//        students.forEach(System.out::println);

        //万能查询2--实例

        String sql = "select * from world where no > ?";

        int params = 2;

        Class<World> clazz = World.class;

        List<World> worlds = query(sql, clazz,params);

        worlds.forEach(System.out::println);

    }


    //...表示不定长，用不定长符号修饰的参数要写到最后一位

    public static <T> List<T> query(String sql, Class<T> clazz, Object... pramas) {

        List<T> list = new ArrayList<>();

        try {

            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

            PreparedStatement ps = connection.prepareStatement(sql);

            if (pramas != null){//当有参数时才执行循环，设置参数这一步骤

                for (int i = 0; i < pramas.length; i++) {

                    ps.setObject(i+1, pramas[i]);//给给定数值设置给定参数的方法，这里不清楚什么类型用Object来概括

                }

            }

            ResultSet resultSet = ps.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集元数据

            int columnCount = metaData.getColumnCount();//获取元数据的列数，就是上面sql中查几列数据的列数

            while (resultSet.next()){
                T s = clazz.newInstance();//每次完成所有的for循环之后，就相当于把所有的元数据列都跑完了一遍，相当于生成了一行数据

                //而sql中一行数据就相当于java里用一个对象来储存

                for (int i = 1; i <= columnCount; i++) {//从1开始

                    String label = metaData.getColumnLabel(i);//因为列名和我们创建的对象的属性名一样，所以这使用label标签名也就是我们的别名

                    try {

                        Field field = clazz.getDeclaredField(label);//每循环一次就能取得一个标签名，而可以通过反射来查找符合这个标签名的字段,没找到就会直接跳过

                        String fieldName = field.getName();//获取本次循环取到的别名的字段名

                        Class<?> fieldType = field.getType();//获取本次循环取到的别名的类型

                        String methodName = "set" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);

                        Method method = clazz.getMethod(methodName, fieldType);

                        Object value = resultSet.getObject(fieldName, fieldType);

                        method.invoke(s,value);

                    } catch (Exception e) {

                        e.printStackTrace();

                    }

                }

                list.add(s);//将反射读取完的值加入集合

            }

            ps.close();

            connection.close();

            resultSet.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return list;//返回这个集合
    }

5.增删改操作

public class Modify {
    private static String URL = "jdbc:mysql://localhost:3306/lesson?severTimezone=Asia/shanghai";
    private static String USER = "root";
    private static String PASSWORD = "root";

    public static void main(String[] args) {

//        //修改操作
//        String sql = "update world set forces = ? where no = ?";

//        Object[] params = {"涂山",4};

//        try {

//            String result = update(sql, params);

//            System.out.println(result);

//        } catch (SQLException e) {

//            e.printStackTrace();
//        }

//        //增加操作
//        String sql = "insert into world values (?,?,?)";

//        Object[] params = {9,"苏雪柔","苏黎市"};

//        try {

//            String result = update(sql,params);

//            System.out.println(result);

//        } catch (SQLException e) {

//            e.printStackTrace();

//        }
        //删除操作

        String sql = "delete from world where wifename = ?";

        String params = "苏雪柔";

        try {

            String result = update(sql,params);

            System.out.println(result);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    public static String update(String sql,Object... params) throws SQLException {

        try {

            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

            PreparedStatement ps = connection.prepareStatement(sql);

            if (params != null){

                for (int i = 0; i < params.length; i++) {

                    ps.setObject(i+1,params[i]);

                }
            }

            int line = ps.executeUpdate();//执行更新操作

            return ("成功修改了" + line + "条数据");

        } catch (SQLException e) {

            throw new RuntimeException(e);

        }
    }
}

6.批量处理batch操作

public class Modify {

    private static String URL = "jdbc:mysql://localhost:3306/lesson?
severTimezone=Asia/shanghai";

    private static String USER = "root";

    private static String PASSWORD = "root";

    public static void main(String[] args) {

        String sql = "insert into hero(name,skill,score,n_no) values(?,?,?,?)";

        List<Object[]> params = Arrays.asList(new Object[]{"李寻欢", "小李飞刀", 78.8, 4}, new Object[]{"张无忌", "乾坤大挪移", 88.8, 5});

        int[] batchUpdate = batchUpdate(sql, params);

        System.out.println(Arrays.toString(batchUpdate));

    }

    //批量处理
    public static int[] batchUpdate(String sql,List<Object[]> params){

        int [] affectRows = null;

        try {

            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

            PreparedStatement ps = connection.prepareStatement(sql);

            if (params != null){

                for (int i = 0; i < params.size(); i++){

                    Object[] objects = params.get(i);//拿到一行数据

                    for (int j = 0; j < objects.length; j++) {

                        ps.setObject(j+1,objects[j]);

                    }

                    ps.addBatch();//添加到预处理的队列中

                }

            }

            affectRows= ps.executeBatch();//执行批处理操作

        } catch (SQLException e) {

            throw new RuntimeException(e);

        }

        return affectRows;

    }

7.对结果集单个数据处理

public class jdbcStep {

    private static String URL = "jdbc:mysql://localhost:3306/lesson?severTimezone=Asia/shanghai";

    private static String USER = "root";

    private static String PASSWORD = "root";

    public static void main(String... args) {

        String sql = "select * from world where no = ?";

        int params = 2;

        Class<World> clazz = World.class;

        World world = find(sql, new SingleResultHander<>(World.class), params);

        System.out.println(world);

    }

    public static <T> T find(String sql,SingleResultHander<T> hander, Object... pramas) {

        T t = null;

        try {

            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

            PreparedStatement ps = connection.prepareStatement(sql);

            if (pramas != null){//当有参数时才执行循环，设置参数这一步骤

                for (int i = 0; i < pramas.length; i++) {

                    ps.setObject(i+1, pramas[i]);//给给定数值设置给定参数的方法，这里不清楚什么类型用Object来概括

                }

            }

            ResultSet rs = ps.executeQuery();

            t = hander.handleResultSet(rs);

            ps.close();

            connection.close();

            rs.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return t;//返回这个集合

    }
    

//接口
package com.zy.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 在一条sql语句执行之后的结果就是一个临时表，这个临时表的信息就存储在ResultSet中
 * 我们查询的结果要么转化为单个对象，要么转化为一个集合，具体如何转换我们不是很清楚，
 * 因此定义了一个对结果集处理的规则，这个规则就存放在ResultSetHander中。
 */

public interface ResultSetHander <T>{

    //T = Student 单个对象
    //T = List<Student> 集合
    //这个接口方法就是对结果集进行处理
    T handleResultSet(ResultSet rs) throws SQLException;

}


//实现类
package com.zy.jdbc;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;

public class SingleResultHander<T> implements ResultSetHander<T> {

    private Class<T> clazz;

    public SingleResultHander(){}

    public SingleResultHander(Class<T> clazz) {

        this.clazz = clazz;

    }

    public Class<T> getClazz() {

        return clazz;

    }

    public void setClazz(Class<T> clazz) {

        this.clazz = clazz;

    }

    @Override
    public T handleResultSet(ResultSet rs) throws SQLException {

        ResultSetMetaData metaData = rs.getMetaData();//获取结果集元数据

        int columnCount = metaData.getColumnCount();//获取元数据的列数，就是上面sql中查几列数据的列数

        T s = null;//单个结果，定义到循环之外

        while (rs.next()) {

            if (s == null) {

                try {

                    s = clazz.newInstance();

                } catch (Exception e) {

                    e.printStackTrace();

                }

                if (clazz.isPrimitive()){

                    T t = rs.getObject(1, clazz);

                } else if (clazz == String.class) {

                    T t = (T) rs.getString(1);

                } else if (clazz == java.sql.Date.class) {

                    T t = (T) rs.getDate(1);

                } else if (clazz == Timestamp.class) {

                    T t = (T) rs.getTimestamp(1);

                } else if (clazz == Time.class) {

                    T t = (T) rs.getTime(1);

                }else {//对象

                    for (int i = 1; i <= columnCount; i++) {//从1开始

                        String label = metaData.getColumnLabel(i);//因为列名和我们创建的对象的属性名一样，所以这使用label标签名也就是我们的别名

                        try {

                            Field field = clazz.getDeclaredField(label);//每循环一次就能取得一个标签名，而可以通过反射来查找符合这个标签名的字段,没找到就会直接跳过

                            String fieldName = field.getName();//获取本次循环取到的别名的字段名

                            Class<?> fieldType = field.getType();//获取本次循环取到的别名的类型

                            String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

                            Method method = clazz.getMethod(methodName, fieldType);

                            Object value = rs.getObject(fieldName, fieldType);

                            method.invoke(s, value);

                        } catch (Exception e) {

                            e.printStackTrace();

                        }
                    }
                }

               } else {

                throw new IllegalArgumentException("查询存在多条匹配结果");

            }
        }

        return s;

    }
}    


//entity--实体类
public class World {

    private int no;

    private String wifename;

    private String forces;

    public int getNo() {
        return no;
    }

    public void setNo(int no) {
        this.no = no;
    }

    public String getWifename() {
        return wifename;
    }

    public void setWifename(String wifename) {
        this.wifename = wifename;
    }

    public String getForces() {
        return forces;
    }

    public void setForces(String forces) {
        this.forces = forces;
    }

    @Override
    public String toString() {
        return "World{" +
                "no=" + no +
                ", wifename='" + wifename + '\'' +
                ", forces='" + forces + '\'' +
                '}';
    }
}