javabean连接数据库删除 java数据库连接增删改查

转载

deanyuancn 2023-07-22 02:31:45

文章标签 javabean连接数据库删除 java 数据库 mysql sql 文章分类 Java 后端开发

1、需要先下载jdbc jar包，然后添加到工程上面

package jdbctest;

import org.junit.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectTest {
        @Test
    public void coonect4() throws Exception {
        // 最终版，将账号信息进行配置化，先在模块下的src下建立配置文件,读取文件
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.propries");
        Properties pros = new Properties();
        pros.load(is);
        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String driver = pros.getProperty("driver");
        //加载驱动
        Class.forName(driver);
        Connection conn =  DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }
}

配置文件

javabean连接数据库删除 java数据库连接增删改查_java

PreparedStatement相比Statement的好处

1. PreparedStatement操作Blob的数据，而Statement做不到。
2. PreparedStatement可以实现更高效的批量操作。

3.解决Statement的拼串、sql问题

对数据库的增删改

package com.ruqi.prepareStatment;
import org.junit.Test;
import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;

public class PrepareStatmentTest {
    // 通用的增删改
    public void updateCommonUpdate(String sql, Object ...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = PrepareStatmentTest.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.execute(); // 查询sql返回true，其他返回false
            ps.executeUpdate(); // 返回更新成功的条数
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            PrepareStatmentTest.closeConnection(conn,ps);
        }
    }

    @Test
    public void updatesql(){
        String sql = "delete from Scores where id = ?;";
        updateCommonUpdate(sql,8);
    }
    

    // 对数据进行增删改操作
    @Test
    public void prepareStatmentUpdate() throws Exception {
        Connection conn = null;
        conn = PrepareStatmentTest.getConnection();
        // 预编译sql，创建preparesstament对象
        String sql = "insert into Scores(id,score,date)values(?,?,?)"; //?表示点位符
        PreparedStatement ps = conn.prepareStatement(sql);
       // 填充点位符
        ps.setInt(1,8); // 下标按从1开始计算，并且每个数据要指定相应类型
        ps.setDouble(2,9.3);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date = sdf.parse("2022-01-01");
        ps.setDate(3, new Date(date.getTime()));
        // 执行sql
        ps.execute();
        PrepareStatmentTest.closeConnection(conn,ps);
    }


    public static Connection getConnection() throws Exception {
        // 获取链接
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.propries");
        Properties pros = new Properties();
        pros.load(is);
        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String driver = pros.getProperty("driver");
        //加载驱动
        Class.forName(driver);
        //获取连接
        Connection conn =  DriverManager.getConnection(url,user,password);
        return conn;
    }

    public static void closeConnection(Connection conn, PreparedStatement ps){
        if(ps!=null){
            try {
                ps.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

对数据库进行查询操作

package com.ruqi.prepareStatment;
import org.junit.Test;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.CookieHandler;
import java.sql.*;
import java.util.Properties;

public class SelectSQL {

    @Test
    public void queryScore() {
        String sql = "select score from Scores where id = ?;";
        ScoresObject score = scoreCommonSelect(sql, 2);
        System.out.println(score);
    }

    // 针对不同表多行数据的通用查询
    public <T> List<T> commonManySelect(Class<T> clazz, String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = ConnectUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData(); 
            ArrayList<T> list = new ArrayList<T>();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < rsmeta.getColumnCount(); i++) {
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmeta.getColumnLabel(i + 1);
                    Field decColumnName = clazz.getDeclaredField(columnLabel);
                    decColumnName.setAccessible(true);
                    decColumnName.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectUtil.closeConnection(conn, ps, rs);
        }
        return null;
    }


// 针对不同表一行数据的通用查询
    public <T> T commonSelect(Class<T> clazz, String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = ConnectUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData(); //获取元数据，通过这个对象获取数据的个数
            if (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < rsmeta.getColumnCount(); i++) {
                    // 获取列值
                    Object columnValue = rs.getObject(i + 1);
                    // 获取列名，不推荐使用
                    // String columnName = rsmeta.getColumnName(i + 1);
                    // 获取别名，为了解决对象属性与数据表的列名不完全一致的情况，
                    // 解决方法是：写sql时，对每个字段取别名，别名要跟对象属性一致，就可以解决上述问题
                    String columnLabel = rsmeta.getColumnLabel(i + 1);
                    Field decColumnName = clazz.getDeclaredField(columnLabel);
                    decColumnName.setAccessible(true);
                    decColumnName.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectUtil.closeConnection(conn, ps, rs);
        }
        return null;
    }


    // 针对一个表一行数据的通用查询
    public ScoresObject scoreCommonSelect(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = ConnectUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData(); //获取元数据，通过这个对象获取数据的个数
            if (rs.next()) {
                ScoresObject soc = new ScoresObject();
                for (int i = 0; i < rsmeta.getColumnCount(); i++) {
                    // 获取列值
                    Object columnValue = rs.getObject(i + 1);
                     // 获取列名，不推荐使用
                    // String columnName = rsmeta.getColumnName(i + 1);
                    // 获取别名，为了解决对象属性与数据表的列名不完全一致的情况，
                    // 解决方法是：写sql时，对每个字段取别名，别名要跟对象属性一致，就可以解决上述问题
                    String columnLabel = rsmeta.getColumnLabel(i + 1);
                    Field decColumnName = ScoresObject.class.getDeclaredField(columnLabel);
                    decColumnName.setAccessible(true);
                    decColumnName.set(soc, columnValue);
                }
                return soc;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectUtil.closeConnection(conn, ps, rs);
        }
        return null;
    }

    @Test
    public void selectSql() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = ConnectUtil.getConnection();
            String sql = "select * from Scores where id = ?;";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,2);
            // 获取结果集
            rs = ps.executeQuery();
            //处理结果集
            if(rs.next()){//判断是否有数据，如果有,指针下移并返回true
                //获取结果集的各个数据
                int id = rs.getInt(1);
                double score = rs.getInt(2);
                Date date = rs.getDate(3);
                //ORM思想：对象关系映射，将每个结果集都放到一个对象，每个对象表示一行数据，对象的数据表示每列的值
                ScoresObject result = new ScoresObject(id, score, date);
                System.out.println(result);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,ps,rs);
        }

    }

}


==================================================

package com.ruqi.prepareStatment;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class ConnectUtil {

    public static Connection getConnection() throws Exception {
        // 获取链接
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.propries");
        Properties pros = new Properties();
        pros.load(is);
        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String driver = pros.getProperty("driver");
        //加载驱动
        Class.forName(driver);
        //获取连接
        Connection conn =  DriverManager.getConnection(url,user,password);
        return conn;
    }

    public static void closeConnection(Connection conn, PreparedStatement ps, ResultSet rs){
        if(ps!=null){
            try {
                ps.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }if(rs!=null){
            try {
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}


====================================================
package com.ruqi.prepareStatment;
import java.math.BigDecimal;
import java.sql.Date;
public class ScoresObject {
    private int id;
    private BigDecimal score;
    private Date date;

    public ScoresObject() {
    }

    public ScoresObject(int id, double score, Date date) {
    }

    public int getId() {
        return id;
    }

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

    public BigDecimal getScore() {
        return score;
    }

    public void setScore(BigDecimal score) {
        this.score = score;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    @Override
    public String toString() {
        return "ScoresObject{" +
                "id=" + id +
                ", score=" + score +
                ", date=" + date +
                '}';
    }
}

操作blob大数据类型

package com.ruqi.prepareStatment;

import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class InsertBlob {


    // 数据库的blob类型分别对应存储的最大值：tinyblob=255b; blob=65K; mediumblob=16M; longblob=4G
    @Test
    public void insertBlob() throws Exception {
        Connection conn = ConnectUtil.getConnection();
        String sql = "insert into Scores(photo) values (?);";
        PreparedStatement ps = conn.prepareStatement(sql);
        FileInputStream inputstream = new FileInputStream(new File("img.png"));
        ps.setBlob(1,inputstream);
        ps.executeUpdate();
        ConnectUtil.closeConnection(conn,ps,null);

    }
    @Test
    public void selectBlob() throws Exception {
        Connection conn = ConnectUtil.getConnection();
        String sql = "select photo from Scores where id = ?;";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,7);
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            Blob photo = rs.getBlob("photo");
            InputStream is = photo.getBinaryStream();
            FileOutputStream fos = new FileOutputStream("test.png");
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) != -1){
                fos.write(buffer,0 ,len);
            }
            fos.close();
            is.close();
        }
        ConnectUtil.closeConnection(conn,ps,rs);


    }
}

批量插入数据

public void insertManyData()  {
        //由于mysql默认不支持批处理，因此需要在配置文件加以下配置url=jdbc:mysql://10.0.2.4:63306/test1111111?rewriteBatchedstatements=true
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = ConnectUtil.getConnection();
            String sql = "insert into Scores (id) values(?);";
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//设置为不自动提前
            for (int i = 0; i <= 20000; i++) {
                ps.setObject(1,i);
                ps.addBatch();//攒sql
                if(i % 500 == 0 ){
                    System.out.println(1);
                    ps.executeBatch(); // 每攒500条执行一次
                    ps.clearBatch();// 清除sql
                }
            }
            conn.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,ps,null);
        }



    }

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java 集合类的面试题 java关于集合的面试题

下一篇：spring boot 富编辑器 springboot整合富文本编辑器

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

javabean连接数据库删除 java数据库连接增删改查

javabean连接数据库删除 java数据库连接增删改查

PreparedStatement相比Statement的好处

1. PreparedStatement操作Blob的数据，而Statement做不到。 2. PreparedStatement可以实现更高效的批量操作。

3.解决Statement的拼串、sql问题

对数据库的增删改

对数据库进行查询操作

操作blob大数据类型

批量插入数据

51CTO博客

1. PreparedStatement操作Blob的数据，而Statement做不到。
2. PreparedStatement可以实现更高效的批量操作。