考试练习

博主： xiaohu
发布时间：2024 年 12 月 16 日
345 次浏览
暂无评论
4594字数
分类：

##A卷
#### sql
```sql

create table stu
(
   sid int  not null primary key,
   sname varchar(20),
   spsw varchar(20)
);

insert into stu values(1,'A1','123');
insert into stu values(2,'A2','123');
insert into stu values(3,'A3','123');
insert into stu values(4,'A4','123');
insert into stu values(5,'A5','123');
```
####-- code --
##### StuDAO
```java
package com.xiaohu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.xiaohu.entity.StuEntity;
import com.xiaohu.utils.DBConn;

public class StuDAO {
	private DBConn db = new DBConn();
	private Connection conn = null;
	private PreparedStatement pre = null;
	private ResultSet rs = null;

//	增
	public void addStu(StuEntity stu) {
		conn = db.GetConn();
		String sql = "insert into stu values(?,?,?)";
		try {
			pre=conn.prepareStatement(sql);
			pre.setInt(1, stu.getSid());
			pre.setString(2, stu.getSname());
			pre.setString(3, stu.getSpsw());
			pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
//	删
	public void delStu(int sid) {
		conn = db.GetConn();
		String sql = "delete  from stu where sid=?";
		try {
			pre=conn.prepareStatement(sql);
			pre.setInt(1, sid);
			pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
//	改
	public void updateStu(int sid,StuEntity stu) {
		conn = db.GetConn();
		String sql = "update stu set sname=?,spsw=? where sid=?";
		try {
			pre = conn.prepareStatement(sql);
			pre.setString(1, stu.getSname());
			pre.setString(2, stu.getSpsw());
			pre.setInt(3, sid);
			pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
//	查
	public List<StuEntity> getAll(){
		conn=db.GetConn();
		String sql="select * from stu";
		List<StuEntity> list = new ArrayList<StuEntity>();
		try {
			pre=conn.prepareStatement(sql);
			rs = pre.executeQuery();
			while(rs.next()) {
				StuEntity stu = new StuEntity();
				stu.setSid(rs.getInt("sid"));
				stu.setSname(rs.getString("sname"));
				stu.setSpsw(rs.getString("spsw"));
				list.add(stu);
			}

} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
//	查询一个
	public StuEntity getOne(int sid) {
		conn = db.GetConn();
		StuEntity stu = new StuEntity();
		String sql = "select * from stu where sid = ?";
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, sid);
			rs = pre.executeQuery();
			while(rs.next()) {
				stu.setSid(rs.getInt("sid"));
				stu.setSname(rs.getString("sname"));
				stu.setSpsw(rs.getString("spsw"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return stu;
	}
}
```
##### StuEntity
```java
package com.xiaohu.entity;

public class StuEntity {
	private int sid;
	private String sname;
	private String spsw;
	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public String getSpsw() {
		return spsw;
	}
	public void setSpsw(String spsw) {
		this.spsw = spsw;
	}
	
}

```
##### DBConn
```java
package com.xiaohu.utils;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConn {
	public Connection GetConn() {
		Connection conn = null;
		try {
			String Driver = "com.mysql.cj.jdbc.Driver";
			String url = "jdbc:mysql://localhost:3306/test";
			String user = "root";
			String password = "root";
			Class.forName(Driver);
			conn = DriverManager.getConnection(url,user,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
}

```

##### Test
```
package com.xiaohu.test;

import java.util.List;

import com.xiaohu.dao.StuDAO;
import com.xiaohu.entity.StuEntity;
import com.xiaohu.utils.DBConn;

public class Test1 {

public static void main(String[] args) {
//		增
		StuDAO stu = new StuDAO();
		StuEntity stuEntity = new StuEntity();
		stuEntity.setSid(1001);
		stuEntity.setSname("xiaoming");
		stuEntity.setSpsw("123456");
		stu.addStu(stuEntity);
		
//		改
		StuEntity stuEntity1 = new StuEntity();
		stuEntity1.setSid(1001);
		stuEntity1.setSname("11xiaoming11");
		stuEntity1.setSpsw("12345611");
		stu.updateStu(1001, stuEntity1);
		
//		查一个
		StuEntity stu1 = stu.getOne(1001);
		System.out.println(stu1.getSid()+"\t"+stu1.getSname());
		
//		删
		stu.delStu(1001);
		
//		查
		List<StuEntity> list = 	stu.getAll();
		for(int i=0;i<list.size();i++) {
			System.out.println(list.get(i).getSname());
		}
		
	}
}

```

##B卷
#### sql
```sql
CREATE TABLE dept(
	pid int PRIMARY KEY COMMENT '部门编号',
	pname varchar(50) COMMENT '部门名称',
	premark varchar(100) COMMENT '备注说明'
);

CREATE TABLE emp(
	eid int PRIMARY KEY COMMENT '员工编号',
	pid int COMMENT '部门编号',
	ename VARCHAR(50) COMMENT '员工姓名',
	epsw VARCHAR(100) COMMENT '员工密码',
	FOREIGN KEY (pid) REFERENCES dept(pid)
);

INSERT INTO dept VALUES(1001,'设计部','暂无'),
									  	(1002,' 产品部','暂无'),
											(1003,'运营部','暂无'),
											(1004,'开发部','暂无');
INSERT INTO emp VALUES (101,1001,'小王','123456'),
												(102,1002,'小李','123456'),	
												(103,1003,'小张','123456'),		
												(104,1004,'小开','123456');
												
SELECT * FROM dept;
SELECT * FROM emp WHERE eid=101;												
```
#### -code-
##### DeptDAO
```java
package org.xiaohu.dao;

import org.xiaohu.entity.DeptEntity;
import org.xiaohu.utils.DBConn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DeptDAO {
    private Connection conn = null;
    private DBConn db;
    public DeptDAO(){
        db  = new DBConn();
    }
//  部门添加
    public void add(DeptEntity dept){
        conn = db.DBConnection();
        String sql = "INSERT INTO dept VALUES(?,?,?)";
        try
        {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setInt(1, dept.getPid());
            pre.setString(2, dept.getPname());
            pre.setString(3, dept.getPremark());
            pre.executeUpdate();
            System.out.println("添加成功");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
//    部门全查询
    public List<DeptEntity> getAllDeptInfo()
    {
        List<DeptEntity> list = new ArrayList<DeptEntity>();

conn = db.DBConnection();
        String sql = "SELECT * FROM dept";
        try
        {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while(rs.next())
            {
                DeptEntity dept = new DeptEntity();
                dept.setPid(rs.getInt("pid"));
                dept.setPname(rs.getString("pname"));
                dept.setPremark(rs.getString("premark"));
                list.add(dept);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return list;
    }
}
```
##### EmpDAO
```java
package org.xiaohu.dao;

import org.xiaohu.entity.EmpEntity;
import org.xiaohu.utils.DBConn;

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

public class EmpDAO {
    private Connection conn = null;
    private DBConn db;
    public EmpDAO()
    {
        db = new DBConn();
    }
    //   查询某个员工
    public EmpEntity getOneEmpInfo(int eid){
        conn = db.DBConnection();//连接数据库
        EmpEntity emp = new EmpEntity();
        String sql = "SELECT * FROM emp WHERE eid=?";
        try
        {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setInt(1, eid);
            ResultSet rs = pre.executeQuery();
            while(rs.next())
            {
                emp.setEid(rs.getInt("eid"));
                emp.setEname(rs.getString("ename"));
                emp.setEpsw(rs.getString("epsw"));
                emp.setPid(rs.getInt("pid"));
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return  emp;
    }
}

```
##### DBConn
```java
package org.xiaohu.utils;

import java.sql.Connection;
import java.sql.DriverManager;

//数据库连接工具类
public class DBConn {
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/xiaohu";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    public Connection DBConnection()
    {
        Connection conn = null;
        try
        {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            if(conn == null){
                System.out.println("数据库连接失败");
            }
            else {
                System.out.println("数据库连接成功");
            }
        }
        return conn;
    }
}

```
##### DeptEntity
```java
package org.xiaohu.entity;

public class DeptEntity {
    private int pid;
    private String pname;
    private String premark;

public int getPid() {
        return pid;
    }

public void setPid(int pid) {
        this.pid = pid;
    }

public String getPname() {
        return pname;
    }

public void setPname(String pname) {
        this.pname = pname;
    }

public String getPremark() {
        return premark;
    }

public void setPremark(String premark) {
        this.premark = premark;
    }
}

```
##### EmpEntity
```java
package org.xiaohu.entity;

public class EmpEntity {
    private int eid;
    private int pid;
    private String ename;
    private String epsw;

public int getEid() {
        return eid;
    }

public void setEid(int eid) {
        this.eid = eid;
    }

public int getPid() {
        return pid;
    }

public void setPid(int pid) {
        this.pid = pid;
    }

public String getEname() {
        return ename;
    }

public void setEname(String ename) {
        this.ename = ename;
    }

public String getEpsw() {
        return epsw;
    }

public void setEpsw(String epsw) {
        this.epsw = epsw;
    }
}
```
##### DeptTest
```java
package org.xiaohu.test;

import org.xiaohu.dao.DeptDAO;
import org.xiaohu.entity.DeptEntity;

import java.util.List;

public class DeptTest {
    public static void main(String[] args) {
        DeptEntity dept = new DeptEntity();
        DeptDAO deptDAO = new DeptDAO();
//        dept.setPid(1005);
//        dept.setPname("测试部");
//        dept.setPremark("测试部");
//        deptDAO.add(dept);

System.out.println("部门编号\t部门名称\t备注说明");
        List<DeptEntity> list  = deptDAO.getAllDeptInfo();
        for(int i = 0;i<list.size();i++){
            System.out.println(list.get(i).getPid()+'\t'+list.get(i).getPname()+'\t'+list.get(i).getPremark());
        }
    }
}

```
##### EmpTest
```java
package org.xiaohu.test;

import org.xiaohu.dao.EmpDAO;
import org.xiaohu.entity.EmpEntity;

public class EmpTest {
    public static void main(String[] args) {
        EmpDAO empDAO = new EmpDAO();
        EmpEntity emp = empDAO.getOneEmpInfo(101);
        System.out.println(emp.getEid()+ emp.getPid()+emp.getEname());
    }
}

```

最后修改：2024 年 12 月 16 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

二十岁啦！
评论数： 5
关于赋值
评论数： 4
HTML
评论数： 1
MySQL学习笔记
评论数： 0
JavaScript学习日志
评论数： 0

小童
哇哦
网友小宋
年轻真好。
优惠券网
写的很详细具体，学习到了，多谢博主的分享！⌇●﹏●⌇
时光本无罪
才二十岁真好
优惠券网
笔记做的很不错，简洁清晰明了！！！୧(๑•̀⌄•́๑)૭

Java 异常
评论数： 0
C++
评论数： 0
Java 集合
评论数： 0
Java 练习(冒泡排序)
评论数： 0
HTML
评论数： 1

考试练习

xiaohu • 2024 年 12 月 16 日

<div id="md_content_3" class="md_content" style="min-height: 50px;"><textarea id="append-test" style="display:none;">##A卷
#### sql
```sql

create table stu
(
   sid int  not null primary key,
   sname varchar(20),
   spsw varchar(20)
);

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.xiaohu.entity.StuEntity;
import com.xiaohu.utils.DBConn;

public class StuDAO {
	private DBConn db = new DBConn();
	private Connection conn = null;
	private PreparedStatement pre = null;
	private ResultSet rs = null;

```
##### DBConn
```java
package com.xiaohu.utils;

import java.sql.Connection;
import java.sql.DriverManager;

```

##### Test
```
package com.xiaohu.test;

import java.util.List;

import com.xiaohu.dao.StuDAO;
import com.xiaohu.entity.StuEntity;
import com.xiaohu.utils.DBConn;

public class Test1 {

```

##B卷
#### sql
```sql
CREATE TABLE dept(
	pid int PRIMARY KEY COMMENT '部门编号',
	pname varchar(50) COMMENT '部门名称',
	premark varchar(100) COMMENT '备注说明'
);

import org.xiaohu.entity.DeptEntity;
import org.xiaohu.utils.DBConn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.xiaohu.entity.EmpEntity;
import org.xiaohu.utils.DBConn;

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

```
##### DBConn
```java
package org.xiaohu.utils;

import java.sql.Connection;
import java.sql.DriverManager;

```
##### DeptEntity
```java
package org.xiaohu.entity;

public class DeptEntity {
    private int pid;
    private String pname;
    private String premark;

public int getPid() {
        return pid;
    }

public void setPid(int pid) {
        this.pid = pid;
    }

public String getPname() {
        return pname;
    }

public void setPname(String pname) {
        this.pname = pname;
    }

public String getPremark() {
        return premark;
    }

public void setPremark(String premark) {
        this.premark = premark;
    }
}

```
##### EmpEntity
```java
package org.xiaohu.entity;

public class EmpEntity {
    private int eid;
    private int pid;
    private String ename;
    private String epsw;

public int getEid() {
        return eid;
    }

public void setEid(int eid) {
        this.eid = eid;
    }

public int getPid() {
        return pid;
    }

public void setPid(int pid) {
        this.pid = pid;
    }

public String getEname() {
        return ename;
    }

public void setEname(String ename) {
        this.ename = ename;
    }

public String getEpsw() {
        return epsw;
    }

public void setEpsw(String epsw) {
        this.epsw = epsw;
    }
}
```
##### DeptTest
```java
package org.xiaohu.test;

import org.xiaohu.dao.DeptDAO;
import org.xiaohu.entity.DeptEntity;

import java.util.List;

```
##### EmpTest
```java
package org.xiaohu.test;

import org.xiaohu.dao.EmpDAO;
import org.xiaohu.entity.EmpEntity;

```</textarea></div>

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

考试练习

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款