day-3-4-5

最新推荐文章于 2024-07-13 18:10:19 发布

I＇m昊帝

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量193

点赞数

分类专栏： java实训文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/haodi12138/article/details/127116109

版权

java实训专栏收录该内容

51 篇文章 0 订阅

订阅专栏

模糊查询问题

模糊查询：select * from tb_emp where name like ‘%龙%’

问题：sql=“select * from tb_emp where name like ‘%?%’”;报错

解决方案1：

String sql="select * from tb_emp where name like ?";
preparedStatement.setString(1,"%龙%");

like ‘abc’ 和 ='abc’从查询结果上来说，没有区别，如果使用模糊查询则传入参数包含通配符；如果是精确等值查询，则不包含通配符

解决方案2：

String sql="select * from tb_emp where name like concat('%',?,'%')";
preparedStatement.setString(1,"龙");

使用系统提供的函数concat用于实现字符串的拼接操作

   public static void main(String[] args) throws Exception {
        String sql="select * from tb_emp where name like concat('%',?,'%')";
        Class.forName("com.mysql.cj.jdbc.Driver");
        try (
                final Connection connection = DriverManager.getConnection("jdbc:mysql:///test?serverTimezone=UTC", "root", "123456");
                final PreparedStatement preparedStatement = connection.prepareStatement(sql);

        ) {
            preparedStatement.setString(1,"龙");
            final ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getLong(1)+"\t"+resultSet.getString(2));
            }
        }
    }

in的用法

select * from tb_emp where id in(1,2,3);

方案1：

long[] arr=new long[] {1L,2L,3L};
StringBuilder sb1=new StringBuilder("select * from tb_emp where id in(");
for(long tmp:arr)
    sb1.append(tmp).append(",");
sb1.deleteCharAt(sb1.length()-1);
String sql=sb1.toString()+")";

方案2：

long[] arr=new long[] {1L,2L,3L};
StringBuilder sb1=new StringBuilder("select * from tb_emp where 1=2 ");
for(long tmp:arr)
    sb1.append(" or id=").append(tmp);

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        long[] arr=new long[] {1L,2L,3L};
        StringBuilder sb1=new StringBuilder("select * from tb_emp where 1=2 ");
        for(long tmp:arr)
            sb1.append(" or id=").append(tmp);
        String sql=sb1.toString();
        System.out.println(sql);
        try (
                final Connection connection = DriverManager.getConnection("jdbc:mysql:///test?serverTimezone=UTC", "root", "123456");
                final PreparedStatement preparedStatement = connection.prepareStatement(sql);

        ) {
            final ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next())
                System.out.println(resultSet.getLong(1)+"\t"+resultSet.getString(2));
        }
    }

获取数据表的元数据

ResultSetMetaData对象保存了所有ResultSet对象中关于字段的信息，提供了对应的方法获取字段相关的信息

int getColumnCount()获取ResultSet对象中的字段个数
String getColumnName(int index)获取ResultSet对象中指定序号对应字段的名称

 public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql="select * from tb_emp";
        try (
                final Connection connection = DriverManager.getConnection("jdbc:mysql:///test?serverTimezone=UTC", "root", "123456");
                final PreparedStatement preparedStatement = connection.prepareStatement(sql);

        ) {
            final ResultSet resultSet = preparedStatement.executeQuery();
            //获取当前表相关的元数据
            final ResultSetMetaData metaData = resultSet.getMetaData();
            int cc=metaData.getColumnCount(); //获取当前表中列的个数
            for(int i=0;i<cc;i++){
                String columnLabel=metaData.getColumnLabel(i+1);
                String columnTypeName = metaData.getColumnTypeName(i + 1);
                System.out.println(columnLabel+"\t"+columnTypeName);
            }
        }
    }