有关数据库连接操作的学习笔记

 一、 基本操作

四条语句:增,删,改,查。

1、#添加数据

insert into student(id , name, gender) values (123456,'张三' ,'男');

2、#删除数据

delete from student where id =1;

3、#修改数据

update student set name = '安琪' ,gender = '女' where id = 2;

4、#查询数据

select * from student ;
select * from student where id= 1;

二、 连接数据库

 

public class Demo1 {
    public static void main(String[] args) throws Exception{
        //注册驱动(其实会自动注册)
        Class.forName("com.mysql.jdbc.Driver");
        //获得数据库的地址
        String url = "jdbc:mysql://localhost:3306/myschool?useSSL=true&characterEncoding=utf8";
        //连接数据库(获得连接)
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        //预编译
        PreparedStatement pstat = conn.prepareStatement("insert into student values(?,?,?)");
        //给预编译的参数赋值
        pstat.setObject(1, 9);
        pstat.setObject(2, "兰陵王");
        pstat.setObject(3, "男");
        //执行数据库操作
        pstat.executeUpdate();
        System.out.println("操作完成");
        //关闭资源
        conn.close();
        pstat.close();


    }
}

注意:执行增删改用的是executeUpdate()方法     查找用executeQuery()返回一个结果集

 

三、 编写工具类(直接上高级点的使用配置文件)

配置文件:db.properties

driver = com.mysql.jdbc.Driver
url = jdbc:mysql:///myschool?useSSL=true&characterEncoding=utf8&rewriteBatchedStament=true
user = root
password = 123456

工具类代码:

public class MyDbUtils {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {
        try {
            Properties properties = new Properties();
            InputStream is = MyDbUtils.class.getClassLoader().getResourceAsStream("db.properties");
            properties.load(is);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获得数据库连接
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }
    //关闭所有连接
    public static void closeAll(Connection conn, Statement stat, ResultSet rs) {
        try {
            if (conn != null) {
                conn.close();
            }
            if (stat != null) {
                stat.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("关闭发生异常");
        }

    }
    //执行  增  删  改 操作------
    public static int execute_Update(String sql, Object[] params) {
        Connection conn = null;
        PreparedStatement pstat = null;
        conn= getConnection();
        try {
            pstat= conn.prepareStatement(sql);
            if (params!=null) {
                for (int i = 0; i < params.length; i++) {
                    //将参数给SQL语句赋值
                    pstat.setObject(i + 1, params[i]);
                }
            }
            return  pstat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn,pstat,null);
        }
        return -1;
    }

    // 执行查询操作   返回一个结果集
    public static <T> List<T> find(String sql, Object[] params, Class<T> clazz) {
        List<T> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        T t = null;

        try {
            conn = getConnection();
            pstat = conn.prepareStatement(sql);
            if (params!=null) {
                for (int i = 0; i < params.length; i++) {
                    pstat.setObject(i + 1, params[i]);
                }
            }
            rs = pstat.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next()) {
                //一共有多少个字段(从1开始)
                t = clazz.newInstance();
                int count = metaData.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    //获取当前字段名
                    String columnLabel = metaData.getColumnLabel(i);
                    //获得当前字段的值
                    Object value = rs.getObject(columnLabel);
                    try {
                        PropertyDescriptor pd = new PropertyDescriptor(columnLabel, clazz);
                        Method writeMethod = pd.getWriteMethod();
                        if (writeMethod != null) {
                            writeMethod.invoke(t, value);
                        }
                    } catch (IntrospectionException e) {
                        continue;
                    }
                }
                list.add(t);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(conn,pstat,rs);

        }

        return list;
    }
}

测试代码:

先编写一个学生类 ------->再在数据库里建立一个student表-------->开始测试:

~~~学生类

//学生类
public class Student {
    private Integer id;
    private String name;
    private String gender;

    public Student() {
    }

    public Student(Integer id, String name, String gender) {
        this.id = id;
        this.name = name;
        this.gender = gender;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

~~~在数据库中建立student表

CREATE TABLE `student` (
  `id` int(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL
);

~~~开始测试

public class MyDbUtilsTest {
    @Test
    public void test1() {
  
        Object[] params = {2, "甄姬", "女"};
        String sql1 = "insert into student values(?,?,?)";
        String sql2 = "select * from student ";
        int i = MyDbUtils.execute_Update(sql1, params);
        System.out.println(i);
        List<Student> students = MyDbUtils.find(sql2, null, Student.class);
        for (Student student : students) {
            System.out.println(student);
        }

    }
}

四、批处理操作(和事务一起)

public class PiChuLi {
    //事务和批处理
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstat = null;
        long star = System.currentTimeMillis();
        try {
            conn = MyDbUtils.getConnection();
            conn.setAutoCommit(false);
            pstat = conn.prepareStatement("insert into student values(?,?,?);");
            for (int i = 1; i <= 100000; i++) {
                pstat.setObject(1, 999);
                pstat.setObject(2, "鲁班七号");
                pstat.setObject(3, "男");
                pstat.addBatch();
                if (i % 1000 == 0) {
                    int[] ints = pstat.executeBatch();
                    System.out.println("长度:"+ints.length);
                    pstat.clearBatch();

                }

            }
            int[] ints = pstat.executeBatch();
            System.out.println(ints.length);
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("总用时:"+(end-star));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MyDbUtils.closeAll(conn,pstat,null);
        }
    }
}

 

五、数据库连接池(以阿里的为例)

~~~配置文件  druid.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool?useSSL=true&characterEncoding=utf8
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最小空闲连接 -->
minIdle=50
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=5000

~~~工具类代码 

public class MyDbUtils_Druid {
    private static DruidDataSource dataSource;
    static {
        try {
            Properties properties = new Properties();
            InputStream is = MyDbUtils_Druid.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            dataSource =(DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("注册驱动失败");
        }

    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

~~~测试代码

public class MyDbUtils_Druid_Test {
    @Test
    public void test1() {
        try {
            Object[] params = {2, "甄姬", "女"};
            String sql1 = "insert into student values(?,?,?)";
            String sql2 = "select * from student ";
            QueryRunner qr = new QueryRunner(MyDbUtils_Druid.getDataSource());
            int i = qr.update(sql1, params);
            List<Student> students = qr.query(sql2, new BeanListHandler<>(Student.class));
            System.out.println(i);
            for (Student student : students) {
                System.out.println(student);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }

    }
}

~~~DBUtils三个常用的类

 

1--ScalarHandler类:适合获取一行一列数据。

​2--BeanHandler类:实现类,把记录转成对象。

​3-BeanListHandler类:实现类,把记录转化成List,使记录为JavaBean类型的对象

~~~注意事项,在数据库连接这一块,一定要记得导包

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值