栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

JAVA封装数据库连接及关闭/增删改查的操作 。

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

JAVA封装数据库连接及关闭/增删改查的操作 。

package com.shuang3.utils;

import com.shuang.connection.ConnectionTest;

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

public class JDBCUtils {

    //读取文件的四个基本信息
  public static Connection getConnection() throws Exception {

      //获取数据库的链接
      InputStream is =  ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
      Properties pros = new Properties();
      pros.load(is);
      //四个基本信息
      String user = pros.getProperty("user");
      String password = pros.getProperty("password");
      String url = pros.getProperty("url");
      String driverClass  =  pros.getProperty("driverClass");
      //加载驱动
      Class.forName(driverClass);
      //获取链接
      Connection conn = DriverManager.getConnection(url,user,password);
      return conn;
  }
  
  
  public void closeResource(Connection conn, Statement ps){
      try {
          if (ps!=null)
          ps.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }
      try {
          if (conn!=null)
          conn.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
}
user=root
password=7364983day
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
driverClass=com.mysql.cj.jdbc.Driver

修改操作:


public class PreparedStatementUpdateTest {

    //增删改
    @Test
    public void testUptate()  {
     //1.获取数据库的连接
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句
            String sql = "update customers set name =? where id = ?" ;
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            ps.setObject(1,"哦XX");
            ps.setObject(2,20);

            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //.关闭操作
            JDBCUtils.closeResource(conn,ps);
        }
    }

通

//利用结果集的 元数据 和 反射 完成通用的Customers查询操作

用增删改:

    public void update(String sql,Object ...args)  {
        //获取数据库的连接
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            //预编译sql语句并返回prepareStatement的实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i = 0 ;i < args.length; i++ ){
                ps.setObject(i+1,args[i]);
            }
            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps);
        }

    }

针对表进行查询

package com.shuang3.preparedstatement.crud;

import com.shuang3.bean.Custmoer;
import com.shuang3.utils.JDBCUtils;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class CustomerForQuery {
@Test
    public void testQuery1()  {
        Connection conn  = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql  = "select id,name,email,birth from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,5);
            //执行,并返回结果集
            resultSet = ps.executeQuery();
            //处理结果集,判断结果集的下一条是否有数据,如果有数据就返回true并使指针下移
            if(resultSet.next()){
                int id  = resultSet.getInt(1);
                String name  = resultSet.getString(2);
                String email  = resultSet.getString(3);
                Date birth  = resultSet.getDate(4);

                //System.out.println("id = " + id + ",name = " + name + ",email = " + email + " , birth = " +birth  );
                //Object[] data  =new Object[]{id,name,email,birth};

                Custmoer custmoer = new Custmoer(id,name,email,birth);

                System.out.println(custmoer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps,resultSet);
        }
    }
}
package com.shuang3.bean;


import java.util.Date;


public class Custmoer {

    private int id ;
    private String name ;
    private String email;
    private Date birth;

    public Custmoer() {

    }

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

    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 getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Custmoer{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", email='" + email + ''' +
                ", birth=" + birth +
                '}';
    }
}
public class CustomerForQuery {
        //利用结果集的 元数据 和 反射 完成通用的Customers查询操作
    @Test
    public  void testQueryForCustomers(){
        String sql = "select id,name,birth,email from customers where id = ? " ;
        Custmoer custmoer = queryForCustomers(sql, 13);
        System.out.println(custmoer);
    }

    @Test
    public Custmoer queryForCustomers(String sql, Object ...args)  {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);

            for(int i = 0 ;i< args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();

            //获取结果集的元数据
            ResultSetMetaData  rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                Custmoer cust  = new Custmoer();
                for(int i = 0 ;i < columnCount;i++){

                    //列的列值
                    Object  columnValue  = rs.getObject(i+1);

                    //获取每个列的列名————————————————————————反射
                    String columnName = rsmd.getColumnName(i + 1);
                    //给cust对象指定的columName属性,赋值为c_Value;
                    Field field = Custmoer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(cust,columnValue);
                    //给cust某个指定的属性赋值为value。
                }
                return cust;
            }
        } catch (Exception e) {
           e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }

 

 

package com.shuang3.preparedstatement.crud;

import com.shuang3.bean.Order;
import com.shuang3.utils.JDBCUtils;
import org.testng.annotations.Test;

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



public class OrderForQuery {
    @Test
    public void testOrderForQuery(){
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
        Order order = orderForQuery(sql, 1);
        System.out.println(order);

    }


    @Test

    public Order orderForQuery(String sql ,Object ...args)  {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            for(int i = 0 ;i 

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/969706.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号