JDBC的使用

于 2020-12-25 15:36:18 发布
阅读量79 收藏
点赞数
分类专栏： Mysql笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_42861021/article/details/111683650
版权

在这里插入图片描述

1.连接数据库

1.maven添加依赖
        <dependency>

        <groupId>mysql</groupId>

        <artifactId>mysql-connector-java</artifactId>

        <version>5.1.25</version>
 2.写配置文件
user=root
password=密码
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库
3.连接获得connection对象

    public  Connection getConnection(){
        if(connection==null){
            synchronized (DbUtil.class)
            {
                if(connection==null){
                    try {
                        Properties properties = new Properties();
                        properties.load(new FileInputStream("src/jdbc.properties"));
                        String url = properties.getProperty("url");
                        connection= DriverManager.getConnection(url,properties);
                    }catch (Exception e)
                    {
                        e.printStackTrace();
                    }
                }

            }
        }
        return  connection;
    }

单条数据增删改

 public  void command(String sql , Object... objects)
    {
        //1.获取数据库的连接
        //取得statement
        //预编译sql语句
        //填充占位符
        //执行
        //关闭资源
        try {
            Connection conn  = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1,objects[i]);
            }
            ps.execute();
            ps.close();
        }catch ( Exception e)
        {
            e.printStackTrace();
        }
    }
    public void update(String sql , Object... objects)
    {
        command(sql,objects);
    }
    public void delete(String sql , Object... objects)
    {
        command(sql,objects);
    }
    public void insert(String sql , Object... objects)
    {
        command(sql,objects);
    }

单条数据select

    public <T>  T selectOne(Class<T> clazz ,String sql , Object... objects)
    {
        try {
            Connection conn  = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1,objects[i]);
            }
            ResultSet resultSet = ps.executeQuery();
            ResultSetMetaData metaData = ps.getMetaData();
            int columnCount = metaData.getColumnCount();
            T t = clazz.newInstance();
            if(resultSet.next()){
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i+1);
                    String columnLabel = metaData.getColumnLabel(i+1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
            }
            ps.close();
            return  t;
        }catch ( Exception e)
        {
            e.printStackTrace();
        }
        return  null;
    }

select多条数据（返回一个List）

    public <T> List<T> selectList(Class<T> clazz , String sql , Object... objects)
    {
        try {
            List<T> list = new ArrayList();
            Connection conn  = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1,objects[i]);
            }
            ResultSet resultSet = ps.executeQuery();
            ResultSetMetaData metaData = ps.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()){
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i+1);
                    String columnLabel = metaData.getColumnLabel(i+1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            ps.close();
            return  list;
        }catch ( Exception e)
        {
            e.printStackTrace();
        }
        return  null;

    }

insert（多条数据+优化）

    @Test
    public void TestInsert()
    {
        String  sql = "insert into player  (playerName) values (?)";

       try {
           Connection  conn = getConnection();
           //设置不允许自动提交数据
           conn.setAutoCommit(false);

           PreparedStatement preparedStatement = conn.prepareStatement(sql);
           for (int i = 0; i < 20000; i++) {
               preparedStatement.setObject(1,"yzm——"+i);
                preparedStatement.addBatch();
                if(i%500==499)
                {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                    System.out.println(i);
                }

           }
       }catch ( Exception e)
       {
           e.printStackTrace();
       }

    }

自己的代码记录

package com.jedi.DB;

import com.jedi.Class.Labor;
import com.jedi.Class.Player;

import java.util.List;

public class DbTest {
    newDbUtil dbUtil= new newDbUtil();

    /*
    * 注册成功返回true，注册失败返回false*/
    public boolean register(String account,String password,String name) {
        try {
            dbUtil.insert("insert into player (account,pass_word,playerName) values(?,?,?);",account,password,name);
            return true;
        }catch (Exception e) {
            return false;
        }
    }
    public Player login(String account,String password)
    {
         return  dbUtil.selectOne(Player.class, "select playerId ,playerName,laborId from player where account=? and pass_word = ?",account,password);
    }
    public  boolean  cancel(int id) {
        try {
            dbUtil.delete("delete from player where playerId = ?",id);
            try {
                dbUtil.delete("delete from labor_player where playerId = ?",id);
            }catch (Exception e) {}
            return true;
        }catch (Exception e) {
            return false;
        }
    }
    public boolean createLabor(int playerId,String  laborName) {
        try {
            dbUtil.insert("insert into labor(laborName,kingId) values (?,?)",laborName,playerId);
            Labor labor = dbUtil.selectOne(Labor.class, "select laborId from labor where laborName = ?", laborName);
            dbUtil.insert("insert into labor_player(laborId,playerId) values (?,?)",labor.getLaborId(),playerId);
            dbUtil.update("update player set laborId = ? where playerId = ?",labor.getLaborId(),playerId);
            return true;
        }catch (Exception e) {
            return false;
        }
    }
    public boolean addLabor(int playerId,int   laborId) {
        try {
            dbUtil.update("update player set laborId = ? where   playerId = ?",laborId,playerId);
            return true;
        }catch (Exception e) {
            return false;
        }
    }

    public List<Player> getPlayers(int laborId) {
        try {
           return dbUtil.selectList(Player.class,"select playerId,playerName,laborId FROM player where laborId=?",laborId);
        }catch (Exception e) {
            return null;
        }
    }

    public static void main(String[] args) {
        for (Player player : new DbTest().getPlayers(3)) {
            System.out.println(player.getId()+player.getName()+player.getLaborId());
        }
    }
}



package com.jedi.DB;

import com.jedi.Class.Player;
import org.junit.Test;

import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class newDbUtil {
    private  Connection connection=null;
    private  PreparedStatement statement =  null;
    public  Connection getConnection(){
        if(connection==null){
            synchronized (newDbUtil.class)
            {
                if(connection==null){
                    try {
                        Properties properties = new Properties();
                        properties.load(new FileInputStream("src/jdbc.properties"));
                        String url = properties.getProperty("url");
                        connection= DriverManager.getConnection(url,properties);
                    }catch (Exception e)
                    {
                        e.printStackTrace();
                    }
                }

            }
        }
        return  connection;
    }
    public void Close() {
        if(connection!=null){
            try {
                connection.close();
            }catch (Exception e){
                e.printStackTrace();
            }
            connection=null;
        }
    }
    public  void command(String sql , Object... objects)
    {
        //1.获取数据库的连接
        //取得statement
        //预编译sql语句
        //填充占位符
        //执行
        //关闭资源
        try {
            Connection conn  = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1,objects[i]);
            }
            ps.execute();
            ps.close();
        }catch ( Exception e)
        {
            e.printStackTrace();
        }
    }
    @Test
    public void testSelectOne(){
        newDbUtil dbUtil = new newDbUtil();
        dbUtil.getConnection();
        Player player = dbUtil.selectOne(Player.class,"select playerId,playerName from player where playerName = ?","小帅哥");
        System.out.println(player.getId()+player.getName());
        dbUtil.Close();
    }
    public <T>  T selectOne(Class<T> clazz ,String sql , Object... objects)
    {
        try {
            Connection conn  = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1,objects[i]);
            }
            ResultSet resultSet = ps.executeQuery();
            ResultSetMetaData metaData = ps.getMetaData();
            int columnCount = metaData.getColumnCount();
            T t = clazz.newInstance();
            if(resultSet.next()){
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i+1);
                    if(columnValue!=null) {
                        String columnLabel = metaData.getColumnLabel(i+1);
                        Field field = clazz.getDeclaredField(columnLabel);
                        field.setAccessible(true);

                        field.set(t,columnValue);
                    }
                }
            }
            ps.close();
            return  t;
        }catch ( Exception e)
        {
            e.printStackTrace();
        }
        return  null;
    }

    @Test
    public void TestSelectList()
    {
        newDbUtil dbUtil = new newDbUtil();
        dbUtil.getConnection();
        List<Player> list = dbUtil.selectList(Player.class,"select playerId,playerName from player where playerId > ?","小帅哥");
        for (Player player : list) {
            System.out.println(player.getId()+player.getName());
        }

        dbUtil.Close();
    }
    public <T> List<T> selectList(Class<T> clazz , String sql , Object... objects)
    {
        try {
            List<T> list = new ArrayList();
            Connection conn  = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1,objects[i]);
            }
            ResultSet resultSet = ps.executeQuery();
            ResultSetMetaData metaData = ps.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()){
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i+1);
                    String columnLabel = metaData.getColumnLabel(i+1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            ps.close();
            return  list;
        }catch ( Exception e)
        {
            e.printStackTrace();
        }
        return  null;

    }
    public void update(String sql , Object... objects)
    {
        command(sql,objects);
    }
    public void delete(String sql , Object... objects)
    {
        command(sql,objects);
    }
    public void insert(String sql , Object... objects)
    {
        command(sql,objects);
    }




    //批量插入数据
    @Test
    public void TestInsert()
    {
        String  sql = "insert into player  (playerName) values (?)";

       try {
           Connection  conn = getConnection();

           //设置不允许自动提交数据
           conn.setAutoCommit(false);

           PreparedStatement preparedStatement = conn.prepareStatement(sql);
           for (int i = 0; i < 20000; i++) {
               preparedStatement.setObject(1,"yzm——"+i);
                preparedStatement.addBatch();
                if(i%500==499)
                {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                    System.out.println(i);
                }

           }
       }catch ( Exception e)
       {
           e.printStackTrace();
       }

    }
    public static void main(String[] args) {
    }

}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值