jdbc学习笔记2statement(使用Java操作数据库)statement与perparedstatement的区别,sql注入,sql预编译,泛型,反射,jdbc实现crud

news/2024/5/19 1:22:48 标签: 数据库, sql, java, jdbc, mysql

jdbc2_0">jdbc学习笔记2

statement(使用Java操作数据库

Statement
上一篇讲到,使用connection建立数据库连接,接下来使用statement类操作数据库实现数据库的增删改查
但是statement有一个安全问题
代码实现如下

String sql = "select * from users where  username= '"+键入内容+"' and userpwd='"+键入内容+"'";  

当有人恶意攻击数据库时,只需添加特殊的sql语句就可以完成对数据库进行增删改查
例如:
键入 ‘1’ 第二个位置键入‘1’ or ‘1’=‘1’;此时sql语句恒为判断正确,输出查询内容具有巨大的安全隐患
把这种过程称为sql注入
故一般不使用statement,使用perparedstatement

PreparedStatement

相对于Statement而言PerparedStatement类更安全,其中为了防止sql注入实现了sql的预编译

sqlsql_16">sql预编译(动态sql语句)

我理解为将所写的sql格式进行实现告知数据库,随后只添加数据,再添加完数据后,数据库,不会将占位符内的内容认定为sql语句,则不会出现sql注入的风险

insert into customers(name,email,birth)value (?,?,?)//添加数据内容待指定
其中问号为占位符

使用PerparedStatement操作数据库

代码实现CRUD-INSERT


public class Preparedstatementtest {
    //preparedstatement
    @Test
    public void test() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
        1,建立数据库连接
            InputStream resourceAsStream = Preparedstatementtest .class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties=new Properties();
            properties.load(resourceAsStream);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");
            Class.forName(driverClass);//注册驱动
            connection = DriverManager.getConnection(url, user, password);//建立连接
            2.预编译sql
            String sql="insert into customers(name,email,birth)value (?,?,?)";
            3.为占位符添加信息
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"吴");
            preparedStatement.setString(2,"w.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date parse = simpleDateFormat.parse("1990-10-09");
            preparedStatement.setString(3, String.valueOf(new java.sql.Date(parse.getTime())));
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
            4.关闭连接器和操作流
                if(preparedStatement!=null)
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection!=null)
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }}

代码实现CRUD-UPDATE

首先将,注册驱动和创建连接进行包装成工具类
代码如下:

public class JdbcUtils {
    public static Connection getConnection() throws Exception {
       //湖区连接
            InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties=new Properties();
            properties.load(resourceAsStream);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");
            Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(url, user, password);
        return  connection;
    }
    public static void closeResourse(Connection connection,PreparedStatement preparedStatement  ){
        //关闭资源
        try {
            if(preparedStatement!=null)
                preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (connection!=null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

接下来实现修改数据库中的记录
代码如下:

                @Test
   public void Updatetest2()  {
       				
                 	   Connection connection = null;
                	   PreparedStatement preparedStatement = null;
                	   try {
                	   	//1.获取连接
                       connection = JdbcUtils.getConnection();
                       // 2.预编译sql语句
                       String sql="update customers set name = ? where id = ?";
                       preparedStatement = connection.prepareStatement(sql);
                       //3.填充占位符
                       preparedStatement.setString(1,"莫扎特");
                       preparedStatement.setInt(2,18);
                       //执行
                       preparedStatement.execute();
                 	  } catch (Exception e) {
                       e.printStackTrace();
                 	  }finally {
                     
                       //4.关闭资源  	
                     	 JdbcUtils.closeResourse(connection,preparedStatement);
                   			}
              }

代码实现CRUD-DELETE

首先完成对增删改的统一抽象方法实现

 public void update(String sql,Object ...args){
       //获取连接
       Connection connection = null;
       PreparedStatement preparedStatement = null;
       try {
           connection = JdbcUtils.getConnection();
           //预编译sql语句
           preparedStatement = connection.prepareStatement(sql);
           //填充占位符
           for (int i = 0; i < args.length; i++) {
               preparedStatement.setObject(i+1,args[i]);
           }
           //执行操作
           preparedStatement.execute();
       } catch (Exception e) {
           e.printStackTrace();
       }finally {
 //关闭资源
          JdbcUtils.closeResourse(connection,preparedStatement);
       }      
}

代码实现删除表中数据

    @Test
   public void deletetest(){
       String sql="delete from customers where id=?";
       update(sql,19);
               						}

JDBC代码实现select

首先包装一个数据表的类

import java.util.Date;

public class Customertest {
   private int id;
   private String name;
   private String email;
   private Date brith;

    public Customertest() {
    }

    public Customertest(int id, String name, String email, Date brith) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.brith = brith;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBrith() {
        return brith;
    }

    public void setBrith(Date brith) {
        this.brith = brith;
    }

    @Override
    public String toString() {
        return "Customertest{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", brith=" + brith +
                '}';
    }
}

实现查询并输出结果集

              @Test
    public void selecttest(){
                    Connection connection = null;
                    PreparedStatement preparedStatement = null;
                    ResultSet resultSet = null;
                    try {
                        connection = JdbcUtils.getConnection();//创建连接
                        String sql ="select id,name,email,birth from customers where id=?";//预编译sql语句
                        preparedStatement = connection.prepareStatement(sql);
                        preparedStatement.setObject(1,1);//填充占位符
                        resultSet = preparedStatement.executeQuery();//执行输出结果集
                        //下面为操作结果集
                        if (resultSet.next()){//判断结果集的下一个位置是否有数据,如果有数据返回true,自动下移,如果没有数据返回flase
                            int id = resultSet.getInt(1);
                            String name = resultSet.getString(2);
                            String email = resultSet.getString(3);
                            java.sql.Date birth = resultSet.getDate(4);
                            Customertest customertest = new Customertest(id, name, email, birth);
                            System.out.println(customertest);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }finally {
                        JdbcUtils.closeResourse2(connection, preparedStatement, resultSet);
                    }
                }

结果为
在这里插入图片描述
实现对某个表通用的查询

      封装查询customer表的方法
               public Customertest queryfrocustomer(String sql,Object...args){
                    Connection connection= null;
                    PreparedStatement preparedStatement = null;
                    ResultSet resultSet= null;
                    try {
                        connection = JdbcUtils.getConnection();
                        preparedStatement = connection.prepareStatement(sql);
                        for (int i = 0; i < args.length; i++) {
                            preparedStatement.setObject(i+1,args[i]);
                        }
                        resultSet = preparedStatement.executeQuery();
                        ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集的元数据
                        int columnCount = metaData.getColumnCount();//获取结果集的列数
                        if (resultSet.next()){
                            Customertest customertest=new Customertest();
                            for (int i = 0; i < columnCount; i++) {
                                Object object = resultSet.getObject(i + 1);
                                //获取每个列的列名给对象赋值
                                String catalogName = metaData.getCatalogName(i + 1);
                                Field declaredField = Customertest.class.getDeclaredField(catalogName);
                                declaredField.setAccessible(true);
                                declaredField.set(customertest,object);

                            }
                            return customertest;
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    }finally {
                        JdbcUtils.closeResourse2(connection,preparedStatement,resultSet);
                    }
                    return null;

                }
                @Test
    public void testquery(){
        String sql ="select id,name from customers where id=?";
                    Customertest qu = queryfrocustomer(sql, 2);
                    System.out.println(qu);
                }

针对不同表的查询操作

import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class PreparedStatementQueryTest {
    @Test
    public void test(){
        String sql="select id,name from customers where id=?";


        Customertest getinstance = getinstance(Customertest.class, sql, 13);
        System.out.println(getinstance);
    }
    public <T> T getinstance(Class<T> clazz,String sql  ,Object...args){
        Connection connection= null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet= null;
        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集的元数据
            int columnCount = metaData.getColumnCount();//获取结果集的列数
            if (resultSet.next()){
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object object = resultSet.getObject(i + 1);
                    //获取每个列的列名给对象赋值
                    String catalogName = metaData.getCatalogName(i + 1);
                    Field declaredField = Customertest.class.getDeclaredField(catalogName);
                    declaredField.setAccessible(true);
                    declaredField.set(t,object);

                }
                return t;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeResourse2(connection,preparedStatement,resultSet);
        }
        return null;
    }
}

总结

使用statement类对数据库进行操作,增删改查crud以及远离加进阶代码实现,包含了反射,泛型等技术
剑谱最终页,无爱即是神
下篇内容学习操作blob类型字段,以及批量插入


http://www.niftyadmin.cn/n/764783.html

相关文章

Attention deep neural network for lane marking detection解析

Abstract&#xff1a; &#xff08;1&#xff09;深度神经网络(DNN)中感受域的经验大小比理论上的要小得多&#xff1b;&#xff08;2&#xff09;我们没有考虑到DNN中每个通道的重要性 语义分割技术在检测目标时会导致错误的车道标记分割位置&#xff0c; 外观线索较少的痕迹…

jdbc学习笔记3,blob类型数据的操作,preparedstatement的批量操作,数据库的事务,以及事务的特性,隔离权限的修改

jdbc学习笔记3 实现操作blob类型的数据 mysql中的blob类型 BLOB是mysql中的大型数据 tinyblob&#xff0c;最大255字节 blob&#xff0c;最大65k mediumblob&#xff0c;最大16m longblob&#xff0c;最大4g 插入blob数据代码实现 import org.junit.Test;import java.io.Fi…

石子合并(区间dp)

设有 N 堆石子排成一排&#xff0c;其编号为 1&#xff0c;2&#xff0c;3&#xff0c;…&#xff0c;N。 每堆石子有一定的质量&#xff0c;可以用一个整数来描述&#xff0c;现在要将这 N 堆石子合并成为一堆。 每次只能合并相邻的两堆&#xff0c;合并的代价为这两堆石子的…

jdbc学习笔记4

jdbc的学习笔记4 数据库连接池 数据库连接池的必要性 java调用drivermanger类时&#xff0c;每次想数据库建立连接时都会将connection加载到内存中&#xff0c;执行完成后再断开连接&#xff0c;这样的方式会导致&#xff0c;会消耗大量的内存和时间&#xff0c;数据库的连接…

P1880 [NOI1995] 石子合并

[NOI1995] 石子合并 - 洛谷 /** Description: To iterate is human, to recurse divine.* Autor: Recursion* Date: 2022-04-02 23:00:48* LastEditTime: 2022-04-05 10:38:03*/ #include <bits/stdc.h> #define LL long long using namespace std; const int maxn 1e…

javaweb学习笔记(html常用标签,css样式,JavaScript基础)数据库约束)

JAVAWEB学习笔记 HTML 通过标签显示在网页的各个部分来显示内容 <!DOCTYPE html><!--声明约束--> <html lang"zh_CN"> <head><!--头部文件&#xff0c;一般包括标题&#xff0c;css样式&#xff0c;js代码--><meta charset"…

P1775 石子合并(弱化版)(区间dp模板)

石子合并&#xff08;弱化版&#xff09; - 洛谷 /** Description: To iterate is human, to recurse divine.* Autor: Recursion* Date: 2022-04-05 10:48:41* LastEditTime: 2022-04-05 11:52:33*/ #include <bits/stdc.h> #define LL long long using namespace std…

javaweb学习笔记2(jquery的使用,以及常用的方法,选择器,过滤器)

javaweb学习笔记2 javascript正则表达式 regfxp对象 方式1&#xff1a; var puttnew RegExp("e");//表示要求字符串中必须包含字符串evar str"abcde";alert(putt.test(str)); 方式2&#xff1a;var putt/e/;var str "abcde";alert(putt.test(…