IDEA连接数据库实现增删改查

努力当一个优秀的程序员

已于 2023-12-26 15:14:39 修改

阅读量819

点赞数 9

文章标签：开发语言数据库 java idea

于 2023-12-26 11:34:09 首次发布

本文链接：https://blog.csdn.net/m0_70078527/article/details/135217926

版权

package grade.score;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import grade.users.User;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import grade.users.User;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class cj {
        //    @Test
        public void selectAll() throws Exception {
//        获取连接
//        加载配置文件
            Properties prop = new Properties();
            prop.load(new FileInputStream("src/config.properties"));
//        获取连接池对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//        获取数据库连接
            Connection conn = dataSource.getConnection();
//        定义sql语句
            String sql="select u_role,u_password,u_name from suser";
//        获取pstmt对象
            PreparedStatement pstms = conn.prepareStatement(sql);
//        执行sql
            ResultSet rs = pstms.executeQuery();
//        处理结果
            User user=null;
            List<User> users=new ArrayList<>();
            while (rs.next()){
//            获取数据
                String uRole = rs.getString("u_role");
                String psd = rs.getString("u_password");
                String uname= rs.getString("u_name");
//            封装对象
                user = new User();
                user.setU_role(uRole);
                user.setU_password(psd);
                user.setU_name(uname);
//            装载集合
                users.add(user);
            }
            System.out.println(users);
//释放资源
            rs.close();
            pstms.close();
            conn.close();
        }
        //    @Test
        public void selectAdd() throws Exception {
//        接受页面提交的参数
            String u_role="student";
            String u_password="2354";
            String u_name="stu";
//        int status=1;//0代表禁用1代表启用
//        获取连接
//        加载配置文件
            Properties prop = new Properties();
            prop.load(new FileInputStream("src/config.properties"));
//        获取连接池对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//        获取数据库连接
            Connection conn = dataSource.getConnection();
//        定义sql语句
            String sql="insert into suser(u_role,u_password,u_name) values (?,?,?)";
//        获取pstmt对象
            PreparedStatement pstms = conn.prepareStatement(sql);
//        设置哦参数
            pstms.setString(1,u_role);
            pstms.setString(2,u_password);
            pstms.setString(3,u_name);

//        执行sql 影响的行数
            int count= pstms.executeUpdate();
//        处理结果
            System.out.println(count>0 ?"成功":"失败");

//释放资源
//        rs.close();
            pstms.close();
            conn.close();
        }
        @Test
        public void selectUpdate() throws Exception {
//        接受页面提交的参数
            String u_role="teacher";
            String u_password="2345";
            String u_name="stu";

//        获取连接
//        加载配置文件
            Properties prop = new Properties();
            prop.load(new FileInputStream("src/config.properties"));
//        获取连接池对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//        获取数据库连接
            Connection conn = dataSource.getConnection();
//        定义sql语句
            String sql="update suser set u_role=?,u_password=? where u_name=?";
//        获取pstmt对象
            PreparedStatement pstms = conn.prepareStatement(sql);
//        设置哦参数
            pstms.setString(1,u_role);
            pstms.setString(2,u_password);
            pstms.setString(3,u_name);


//        执行sql 影响的行数
            int count= pstms.executeUpdate();
//        处理结果
            System.out.println(count>0 ?"成功":"失败");

//释放资源
//        rs.close();
            pstms.close();
            conn.close();
        }
        //    @Test
        public void selectDelete() throws Exception {
//        接受页面提交的参数
            String u_name="stu";
//        int status=1;//0代表禁用1代表启用
//        获取连接
//        加载配置文件
            Properties prop = new Properties();
            prop.load(new FileInputStream("src/config.properties"));
//        获取连接池对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//        获取数据库连接
            Connection conn = dataSource.getConnection();
//        定义sql语句
            String sql="delete from suser where u_name=?";
//        获取pstmt对象
            PreparedStatement pstms = conn.prepareStatement(sql);
//        设置哦参数
            pstms.setString(1,u_name);


//        执行sql 影响的行数
            int count= pstms.executeUpdate();
//        处理结果
            System.out.println(count>0 ?"成功":"失败");

//释放资源
//        rs.close();
            pstms.close();
            conn.close();
        }
    }

/*
MySQL Data Transfer
Source Host: localhost
Source Database: studentsystem
Target Host: localhost
Target Database: studentsystem
Date: 2013-04-04 23:41:13
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_type` varchar(20) default NULL,
  `c_grade` float(10,0) default NULL,
  `c_term` varchar(20) default NULL,
  `c_address` varchar(50) default NULL,
  `c_time` varchar(20) default NULL,
  `c_week` varchar(20) default NULL,
  `c_teacher` int(20) default NULL,
  `c_name` varchar(20) NOT NULL,
  `c_id` int(20) NOT NULL,
  PRIMARY KEY  (`c_id`),
  KEY `cc` (`c_teacher`),
  CONSTRAINT `cc` FOREIGN KEY (`c_teacher`) REFERENCES `suser` (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for notice
-- ----------------------------
DROP TABLE IF EXISTS `notice`;
CREATE TABLE `notice` (
  `n_content` varchar(400) default NULL,
  `n_people` varchar(20) NOT NULL,
  `n_time` varchar(20) NOT NULL,
  `n_title` varchar(50) NOT NULL,
  `n_id` int(20) NOT NULL auto_increment,
  PRIMARY KEY  (`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `score` int(20) default NULL,
  `s_cid` int(20) NOT NULL,
  `s_sid` int(20) NOT NULL,
  `s_id` int(20) NOT NULL,
  PRIMARY KEY  (`s_id`),
  KEY `s_cid` (`s_cid`),
  KEY `ssd` (`s_sid`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`s_cid`) REFERENCES `course` (`c_id`),
  CONSTRAINT `ssd` FOREIGN KEY (`s_sid`) REFERENCES `suser` (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for suser
-- ----------------------------
DROP TABLE IF EXISTS `suser`;
CREATE TABLE `suser` (
  `u_identity` varchar(20) NOT NULL,
  `u_birthday` varchar(20) NOT NULL,
  `u_role` varchar(20) NOT NULL,
  `u_nation` varchar(10) NOT NULL,
  `u_college` varchar(50) NOT NULL,
  `u_major` varchar(50) default NULL,
  `u_sex` varchar(2) NOT NULL,
  `u_password` varchar(20) NOT NULL,
  `u_name` varchar(20) NOT NULL,
  `u_id` int(20) NOT NULL auto_increment,
  PRIMARY KEY  (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `course` VALUES ('xuan', '8', '43s', 's222', '8:00', 'w3', '35', 'js', '2');
INSERT INTO `course` VALUES ('jkl', '7', '44d', 's32', '8', 'w5', '35', 'ssu', '3');
INSERT INTO `course` VALUES ('xuan', '8', '64s', 's345', '9:00', 'w3', '30', 'aa', '4');
INSERT INTO `course` VALUES ('hd', '10', '64s', 's345', '9:00', 'w3', '36', 'lishir', '5');
INSERT INTO `course` VALUES ('bixiu', '8', '43s', 's333', '7:00', 'w3', '34', 'dev', '6');
INSERT INTO `course` VALUES ('bixiu', '10', '44s', 's3112', '9:00', 'w4', '36', 'kav', '7');
INSERT INTO `course` VALUES ('bixiu', '8', '44s', 's1235', '8:00', 'w1', '37', 'daji', '8');
INSERT INTO `course` VALUES ('bixiu', '8', '44s', 's1235', '7:00', 'w2', '37', 'daji', '9');
INSERT INTO `course` VALUES ('bixiu', '8', '43s', 's302', '8:00', 'w3', '36', 'css', '10');
INSERT INTO `course` VALUES ('bui', '8', '44s', 's321', '8:00', 'w5', '36', 'qwe', '11');
INSERT INTO `course` VALUES ('ds', '8', '44s', 's3214', '8', 'w2', '37', 'dajia', '12');
INSERT INTO `course` VALUES ('ke', '8', '43s', 's324', '8', 'w3', '14', 'kexg', '13');
INSERT INTO `course` VALUES ('jj', '10', '54s', 's432', '9', 'w1', '37', 'ccc', '14');
INSERT INTO `course` VALUES ('', '0', '', '', '', '', null, 'java', '15');
INSERT INTO `course` VALUES ('zi', '5', '34', '8', 's333', 'w3', '16', 'sef', '16');
INSERT INTO `course` VALUES ('xuan', '8', '33s', 's343', '8:30', 'w1', null, 'sd', '17');
INSERT INTO `course` VALUES ('re', '8', '45s', 's343', '8:00', 'e3', null, 'qwe', '18');
INSERT INTO `course` VALUES ('re', '7', '54s', 's33', '5', 'w2', null, '方法', '19');
INSERT INTO `course` VALUES ('', '0', '', '', '', '', '114', 'ssf', '20');
INSERT INTO `course` VALUES ('', '0', '', '', '', '', '115', 'cdd', '21');
INSERT INTO `course` VALUES ('', '0', '', '', '', '', '117', '政治', '22');
INSERT INTO `course` VALUES ('必修', '10', '64学时', '南三102', '8:30', '周三', '117', '政治', '23');
INSERT INTO `course` VALUES ('bixiu ', '8', '64s', 's3012', '8:00', 'week2', '37', 'css', '24');
INSERT INTO `course` VALUES ('选修', '10', '64s', '南2021', '8:00', '周三', '36', '政治', '25');
INSERT INTO `course` VALUES ('xx', '8', '64s', 's3012', '8:00', 'week2', '36', 'java', '26');
INSERT INTO `course` VALUES ('选修', '8', '54s', 's2312', '3', 'w3', '117', '数学', '27');
INSERT INTO `course` VALUES ('re', '8', '45s', 's333', '6', 'w3', '36', 'dd', '28');
INSERT INTO `notice` VALUES ('即将开放sss', '王可', '2013-04-04 01:09', '新课', '7');
INSERT INTO `notice` VALUES ('进入ddd', '人人', '2013-04-04 01:40', '新开始', '8');
INSERT INTO `notice` VALUES ('hello hi hi hi', 'kk', '2013-04-04 01:46', 'jinxin', '9');
INSERT INTO `notice` VALUES ('防撒安放盛大发', '塞规', '2013-04-04 08:54', '开发', '10');
INSERT INTO `notice` VALUES ('士大夫', ' 十分上', '2013-04-04 13:03', '但是', '11');
INSERT INTO `score` VALUES ('54', '4', '40', '3');
INSERT INTO `score` VALUES ('44', '5', '40', '4');
INSERT INTO `score` VALUES ('70', '6', '40', '5');
INSERT INTO `score` VALUES ('25', '6', '25', '6');
INSERT INTO `score` VALUES ('70', '6', '15', '7');
INSERT INTO `score` VALUES ('50', '8', '17', '9');
INSERT INTO `score` VALUES ('80', '5', '15', '10');
INSERT INTO `score` VALUES ('80', '14', '46', '15');
INSERT INTO `score` VALUES ('100', '10', '46', '16');
INSERT INTO `score` VALUES (null, '13', '46', '17');
INSERT INTO `score` VALUES (null, '13', '46', '18');
INSERT INTO `score` VALUES (null, '11', '46', '19');
INSERT INTO `score` VALUES ('60', '12', '46', '20');
INSERT INTO `score` VALUES ('70', '9', '46', '21');
INSERT INTO `score` VALUES (null, '16', '46', '23');
INSERT INTO `score` VALUES (null, '3', '46', '25');
INSERT INTO `score` VALUES (null, '4', '46', '26');
INSERT INTO `score` VALUES (null, '6', '46', '27');
INSERT INTO `suser` VALUES ('420117198901187111', '2011-11', 'student', '\'ka\'', 'hanko', 'cyu', '?', '187000', 'lisi', '10');
INSERT INTO `suser` VALUES ('420117198901187222', '2011-11', 'student', '\'han\'', 'jike', 'jis', '?', '187222', 'zhanga', '12');
INSERT INTO `suser` VALUES ('420117198901187123', '2010-11', 'teacher', '\'sang\'', 'jike', null, '?', '187123', 'teacher1', '13');
INSERT INTO `suser` VALUES ('420117198901187361', '2008-11', 'teacher', '\'han\'', 'jik', null, '?', '187361', 'teacher2', '14');
INSERT INTO `suser` VALUES ('420117198801187000', '2012-11', 'student', '\'han\'', 'jik', 'sa', '?', '187000', 'student1', '15');
INSERT INTO `suser` VALUES ('420117194601187000', '2012-11', 'teacher', '\'han\'', 'jik', null, '?', '187000', 'teacher3', '16');
INSERT INTO `suser` VALUES ('420117194601187984', '2008-11', 'student', '\'han\'', 'jike', 'sa', '?', '187984', 'stu2', '17');
INSERT INTO `suser` VALUES ('420117194601187154', '2010-11', 'student', '\'han\'', 'jik', 'sa', '?', '187154', 'stua', '18');
INSERT INTO `suser` VALUES ('420117194701147000', '2010-11', 'student', '\'dab\'', 'gh', 'jl', '?', '147000', 'stu3', '19');
INSERT INTO `suser` VALUES ('42011719460118724', '2008-11', 'teacher', '\'han\'', 'fg', null, '?', '118724', 'teacher4', '20');
INSERT INTO `suser` VALUES ('420117194601183400', '2008-11', 'teacher', '\'han\'', 'jike', null, '?', '183400', 'teacher5', '21');
INSERT INTO `suser` VALUES ('420117198901281864', '2008-11', 'teacher', '\'sang\'', 'jike', null, '?', '281864', 'teacher6', '22');
INSERT INTO `suser` VALUES ('420117198901281864', '2011-11', 'teacher', '\'han\'', 'ds', null, '?', '281864', 'teacher6', '23');
INSERT INTO `suser` VALUES ('420117198901287986', '2009-12', 'student', '\'as\'', 'as', 'asd', '?', '287986', 'zys', '24');
INSERT INTO `suser` VALUES ('420117145601287111', '2012-12', 'student', '\'sdf\'', 'sdf', 'sdf', '?', '287111', 'dfs', '25');
INSERT INTO `suser` VALUES ('420117198901284561', '2012-12', 'student', '\'dab\'', 'df', 'ds', '?', '284561', 'gfgfhg', '26');
INSERT INTO `suser` VALUES ('420117198901212345', '2009-12', 'student', '\'han\'', 'jike', 'shu', '?', '212345', 'xiaoming', '27');
INSERT INTO `suser` VALUES ('420117201351212345', '2011-12', 'student', 'han', 'likeas', 'wen', '?', '212345', 'stu11', '29');
INSERT INTO `suser` VALUES ('420117198901215461', '2010-12', 'teacher', 'han', 'gh', null, '?', '215461', 'tea2', '30');
INSERT INTO `suser` VALUES ('420117198901218521', '2010-12', 'teacher', 'han', 'hanko', null, '?', '218521', 'tea4', '31');
INSERT INTO `suser` VALUES ('420117198901211245', '2010-12', 'teacher', 'han', 'jik', null, '?', '211245', 'tea3', '32');
INSERT INTO `suser` VALUES ('420117198901212348', '2009-12', 'teacher', 'han', 'hanko', null, '?', '212348', 'tea5', '33');
INSERT INTO `suser` VALUES ('420117198901219635', '2011-12', 'teacher', 'han', 'jik', null, '?', '219635', 'tea6', '34');
INSERT INTO `suser` VALUES ('420117198901282346', '2010-12', 'teacher', 'zang', 'jike', null, '?', '111', 'tea7', '35');
INSERT INTO `suser` VALUES ('420117198901217515', '2009-12', 'teacher', 'zang', 'jik', null, '?', '217515', 'tea8', '36');
INSERT INTO `suser` VALUES ('420117198901217546', '2009-17', 'teacher', 'dads', '武汉科技大学', null, '?', '111', 'tea12', '37');
INSERT INTO `suser` VALUES ('420117198901187000', '2012-12', 'student', 'wer', 'wer', 'wer', '?', '187000', 'stuwe', '39');
INSERT INTO `suser` VALUES ('420117198901187000', '2012-12', 'student', 'rd', 'fdg', 'dr', '?', '187000', 'ert', '40');
INSERT INTO `suser` VALUES ('420117198901187456', '2013-04', 'student', 'r', 'yt', 'rr', '?', '187456', 'fhy', '42');
INSERT INTO `suser` VALUES ('4211171989011123456', '1990-22', 'student', '藏', '武汉大学', '土木', '男', '111', '李新', '46');
INSERT INTO `suser` VALUES ('420117199005227132', '1986-10', 'admin', '汉', '理工大', null, '男', '111', '李兴', '111');
INSERT INTO `suser` VALUES ('420117198904187111', '1989-04', 'student', '汉', '武汉大学', '机械', '男', '187111', '张三', '112');
INSERT INTO `suser` VALUES ('420117198901187111', '2013-03', 'teacher', 'g', 'sdfs', null, '男', '187111', 'wef', '114');
INSERT INTO `suser` VALUES ('420117198603187121', '2012-12', 'teacher', 'hs', 'fgd', null, '男', '187121', 'qwe', '115');
INSERT INTO `suser` VALUES ('420117198901112354', '1988-12', 'teacher', '汉', '武汉科技大学', null, '男', '112354', '李军', '117');