Java MySQL数据库操作

怼烎@ 2022-06-02 09:57 185阅读 0赞

关于MySQL的基础的四种操作

亲手封装的工具类

package com.wkk.util;
    
    import java.lang.reflect.Field;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.UUID;
    
    public class DBUtil {
        
    
        /**
         * 默认链接
         *
         * @return
         */
        public static Connection getConnection() {
            return getConnection("localhost", "3306", "wkk", "root", "");
        }
    
        /**
         * @param ip
         * @param port
         * @param dbName
         * @param user
         * @param password
         * @return
         */
        public static Connection getConnection(String ip, String port, String dbName, String user, String password) {
            Connection connection = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection(
                        "jdbc:mysql://" + ip + ":" + port + "/" + dbName + "?useUnicode=true&characterEncoding=utf8", user, password);
    
            } catch (Exception e) {
                e.printStackTrace();
            }
            return connection;
        }
    
        /**
         * 执行sql语句
         *
         * @param sql
         * @return
         */
        public static boolean executeSQL(String sql) {
            Connection connection = getConnection();
            boolean result = false;
            try {
                Statement statement = connection.createStatement();
                statement.executeUpdate(sql);
                statement.close();
                result = true;
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return result;
        }
    
        /**
         * 获取唯一ID
         *
         * @return
         */
        public static String getID() {
            return UUID.randomUUID().toString().replace("-", "");
        }
    
        /**
         * 根据sql装载对象
         *
         * @param sql
         * @param class_
         */
        public static Object loadObject(String sql, Class class_) {
            Connection connection = getConnection();
            Object object = null;
            try {
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
                if (resultSet.next()) {
                    object = load(resultSet, class_);
                }
                resultSet.close();
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return object;
        }
    
        /**
         * 装载集合
         *
         * @param sql
         * @param class_
         * @return
         */
        public static List loadArray(String sql, Class class_) {
            Connection connection = getConnection();
            List<Object> list = new ArrayList<>();
            try {
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
                while (resultSet.next()) {
                    Object object = load(resultSet, class_);
                    list.add(object);
                }
                resultSet.close();
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return list;
        }
    
        private static Object load(ResultSet resultSet, Class class_) {
            Object object = null;
            try {
                object = class_.newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if (object == null)
                return null;
    
            ResultSetMetaData resultSetMetaData = null;
            try {
                resultSetMetaData = resultSet.getMetaData();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            int columnCount = 0;
            try {
                columnCount = resultSetMetaData.getColumnCount();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
    
            Field fields[] = class_.getDeclaredFields();
            Map<String, Field> map = new HashMap<>();
            for (Field field : fields) {
                map.put(field.getName().toUpperCase(), field);
            }
    
            for (int i = 0; i < columnCount; i++) {
                try {
                    String name = resultSetMetaData.getColumnName(i + 1);
                    Field field = map.get(name.toUpperCase());
                    if (field == null) {
                        continue;
                    }
                    String simpleName = field.getType().getSimpleName();
                    Method vMethod = ResultSet.class.getDeclaredMethod("get" + simpleName.substring(0, 1).toUpperCase() + simpleName.substring(1), int.class);
                    Object value = vMethod.invoke(resultSet, i + 1);
                    Method method = class_.getDeclaredMethod("set" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1), field.getType());
                    method.invoke(object, value);
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return object;
        }
    
        /**
         * 获取单一值
         */
        public static Object getValue(String sql) {
            Connection connection = getConnection();
            Object object = null;
            try {
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
                if (resultSet.next()) {
                    object = resultSet.getObject(1);
                }
                resultSet.close();
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return object;
        }
    
        /**
         * 批量操作
         */
        public static boolean batchUpdate(String sql, int count, BatchSetter batchSetter) {
            Connection connection = DBUtil.getConnection();
            try {
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                for (int i = 0; i < count; i++) {
                    batchSetter.setValues(preparedStatement,i);
                    preparedStatement.addBatch();
                }
                preparedStatement.executeBatch();
                connection.commit();
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return false;
        }
    
        /**
         * 批处理设置值的接口
         */
        public interface BatchSetter {
        
            void setValues(PreparedStatement preparedStatement, int i);
        }
    
    
    }

## 1.创建一个测试表 ##

create table user(
    userID varchar(32) ,
    name varchar(30),
    phone varchar(11),
    password varchar(30),
    createTime datetime,
    primary key(userID)
    )charset=utf8 ;

## 2.对应的实体类 ##

package com.wkk.entity;
    
    public class User {
        
    
        private String userID;
        private String name;
        private String phone;
        private String password;
        private String createTime;
    
        public String getUserID() {
            return userID;
        }
    
        public void setUserID(String userID) {
            this.userID = userID;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getPhone() {
            return phone;
        }
    
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getCreateTime() {
            return createTime;
        }
    
        public void setCreateTime(String createTime) {
            this.createTime = createTime;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "userID='" + userID + '\'' +
                    ", name='" + name + '\'' +
                    ", phone='" + phone + '\'' +
                    ", password='" + password + '\'' +
                    ", createTime='" + createTime + '\'' +
                    '}';
        }
    }

## 3.对数据库操作的接口和实现类 ##

package com.wkk.entity.dao;
    
    import com.wkk.entity.User;
    
    import java.util.List;
    
    public interface UserDao {
        
        /**
         * 创建
         *
         * @param user
         * @return
         */
        boolean createUser(User user);
    
        /**
         * 删除
         *
         * @param userID
         * @return
         */
        boolean delete(String userID);
    
        /**
         * 修改名称
         *
         * @param userID
         * @param name
         * @return
         */
        boolean updateUserName(String userID, String name);
    
        /**
         * 根据用户ID查询
         *
         * @param userID
         * @return
         */
        User getUser(String userID);
    
        /**
         * 获取user表中总记录数
         *
         * @return
         */
        long getCount();
    
        /**
         * 随机获取一条数据
         *
         * @return
         */
        User getRandomUser();
    
        /**
         * 获取全部
         * @return
         */
        List<User> getAll();
    }

package com.wkk.entity.dao.impl;
    
    import com.wkk.db.DBUtil;
    import com.wkk.entity.User;
    import com.wkk.entity.dao.UserDao;
    
    import java.util.List;
    
    public class UserDaoImpl implements UserDao {
        
    
    
        @Override
        public boolean createUser(User user) {
            String sql = "insert into  user (userID,name,phone,password,createTime) value " +
                    "('" + DBUtil.getID() + "','" + user.getName() + "','" + user.getPhone() + "','" + user.getPassword() + "',now());";
            return DBUtil.executeSQL(sql);
        }
    
        @Override
        public boolean delete(String userID) {
            String sql = "delete from user where userID='" + userID + "';";
            return DBUtil.executeSQL(sql);
        }
    
        @Override
        public boolean updateUserName(String userID, String name) {
            String sql = "update user set name='" + name + "' where userID='" + userID + "';";
            return DBUtil.executeSQL(sql);
        }
    
        @Override
        public User getUser(String userID) {
            String sql = "select * from user where userID='" + userID + "' ;";
            return (User) DBUtil.loadObject(sql, User.class);
        }
    
        @Override
        public long getCount() {
            String sql = "select count(*) from user;";
            return (long) DBUtil.getValue(sql);
        }
    
        @Override
        public User getRandomUser() {
            String sql = "select * from user  order  by rand() limit 1;";
            return (User) DBUtil.loadObject(sql, User.class);
        }
    
        @Override
        public List<User> getAll() {
            String sql = "select * from user";
            return DBUtil.loadArray(sql, User.class);
        }
    
    
    }

## 4.测试 ##

package com.wkk.db;
    
    import com.wkk.entity.User;
    import com.wkk.entity.dao.UserDao;
    import com.wkk.entity.dao.impl.UserDaoImpl;
    
    import java.util.List;
    import java.util.Random;
    
    public class Test {
    
        public static void main(String agr[]) {
            UserDao userDao = new UserDaoImpl();
            //1.初始化10条数据
            for (int i = 0; i < 10; i++) {
        
                User user = new User();
                user.setPassword("888888");
                user.setPhone(getPhone());
                user.setName(getName());
                userDao.createUser(user);
            }
            //2.查询总记录条数
            System.out.println(userDao.getCount());
            //3.随机查询一条数据
            User user = userDao.getRandomUser();
            System.out.println(user);
            //4.删除
            userDao.delete(user.getUserID());
            System.out.println(userDao.getCount());
            //5.修改
            user = userDao.getRandomUser();
            System.out.println(user);
            userDao.updateUserName(user.getUserID(), "李四");
            //6.查询
            user = userDao.getUser(user.getUserID());
            System.out.println(user);
            System.out.println();
            //7.查询全部
            List<User> list = userDao.getAll();
            for (User user1 : list) {
                System.out.println(user1);
            }
        }
    
        public static String getName() {
            String name = "";
            for (int i = 0; i < 2; i++) {
        
                name += new String(new char[]{(char) (new Random().nextInt(20902) + 19968)});
            }
            return name;
        }
    
        public static String getPhone() {
            String phone = "";
            for (int i = 0; i < 11; i++) {
        
                phone += String.valueOf((int) (Math.random() * 10));
            }
            return phone;
        }
    }

运行结果:  
![SouthEast][]

10
    User{
         userID='055226a0de1548e9abb6bb130ad56373', name='姀莵', phone='72417557386', password='888888', createTime='2018-01-10 12:18:32.0'}
    9
    User{
         userID='510831be2c3c4a40a157ae378834d474', name='燔蠭', phone='94456920210', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='510831be2c3c4a40a157ae378834d474', name='李四', phone='94456920210', password='888888', createTime='2018-01-10 12:18:32.0'}
    
    User{
         userID='0284a7411c63489d85ac73145b18aad5', name='雠鉘', phone='71624562102', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='0e95276060fb402a9de985df4623d648', name='辜腭', phone='58807577008', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='181b27acfb474933a92a279e4c6eda78', name='聿飜', phone='08291959176', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='1a3d161ebcc842e99d51eaffa05a3173', name='鞛釚', phone='39505832540', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='2aae35c5ff094aecbde56f7ab76e3402', name='燍薈', phone='58669312637', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='2d29f73665b44d6a98f377345437c1a7', name='妀秛', phone='69024250227', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='510831be2c3c4a40a157ae378834d474', name='李四', phone='94456920210', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='976960c0a7ea418ca976d472af67a3e1', name='笣叡', phone='50958385645', password='888888', createTime='2018-01-10 12:18:32.0'}
    User{
         userID='af860f1e12e84014bd2f2d4f8c7bd26e', name='漕鑛', phone='49590492862', password='888888', createTime='2018-01-10 12:18:32.0'}
    
    Process finished with exit code 0

欢迎交流，与君共勉

[SouthEast]: /images/20220602/7096cb5b2066440cb74965cc2c3f4bbd.png