1、下载myeclipse并激活
这里提供下载文件和激活文件
myeclipse: https://pan.baidu.com/s/1XRnU1vjA-dUaEoLV7A7d4Q 提取码: 53q0
激活文件: https://pan.baidu.com/s/1P9fvQMuutNIw2lXtBc-2tQ 提取码: r103
如果没有sqlserver也可以提供: https://pan.baidu.com/s/1dRGl8P8tNB4LLlMhM2tmQQ 提取码: 0488
2、设置好自己sqlsever的登录名和密码,具体过程可以百度。这里有百度链接:我的登录名和密码是“sa”、“123”。新建一个数据库,命名为“学生成绩管理系统”
3、下载一个sqlserver的jar包,导入到新建的Java项目中。
定义增删改查等功能的函数:
package jdbc;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.sql.*;
public class JdbcUtils {
// 表示定义数据库的用户名
private final String USERNAME = “sa”;
// 定义数据库的密码
private final String PASSWORD = “123”;
// 定义数据库的驱动信息
private final String DRIVER = “com.microsoft.sqlserver.jdbc.SQLServerDriver”;
// 定义访问数据库的地址
private final String URL = “jdbc:sqlserver://localhost:1433;DatabaseName=学生成绩管理系统”;
// 定义数据库的链接
private Connection connection;
// 定义sql语句的执行对象
private PreparedStatement pstmt;
// 定义查询返回的结果集合
private ResultSet resultSet;
// 实现批处理操作的功能
private Statement stmt;
public JdbcUtils() {
try {
Class.forName(DRIVER);
System.out.println(“注册驱动成功!!”);
} catch (Exception e) {
System.out.println(“注册驱动失败!!”);
// TODO: handle exception
}
}
// 定义获得数据库的链接
public Connection getConnection() {
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// System.out.println(“数据库连接成功!!”);
} catch (Exception e) {
// System.out.println(“数据库连接失败!!”);
// TODO: handle exception
}
return connection;
}
public boolean deleteByBatch(String[] sql) throws SQLException{
boolean flag = false;
stmt = connection.createStatement();
if(sql!=null){
for(int i=0;i<sql.length;i++){
stmt.addBatch(sql[i]);
}
}
int[] count = stmt.executeBatch();
if(count!=null){
flag = true;
}
return flag;
}
/**
- 完成对数据库的表的添加删除和修改的操作
- @param sql
- @param params
- @return
- @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List params)
throws SQLException {
boolean flag = false;
int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
pstmt = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;
}
/**
- 查询返回单条记录
- @param sql
- @param params
- @return
- @throws SQLException
/
public Map<String, Object> findSimpleResult(String sql, List params)
throws SQLException {
Map<String, Object> map = new HashMap<String, Object>();
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();// 返回查询结果
ResultSetMetaData metaData = resultSet.getMetaData();
int col_len = metaData.getColumnCount();// 获得列的名称
while (resultSet.next()) {
for (int i = 0; i < col_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = “”;
}
map.put(cols_name, cols_value);
}
}
return map;
}/* - 查询返回多行记录
- @param sql
- @param params
- @return
- @throws SQLException
*/
public List<Map<String, Object>> findMoreResult(String sql,
List params) throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = “”;
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
// jdbc的封装可以用反射机制来封装:
public T findSimpleRefResult(String sql, List params,
Class cls) throws Exception {
T resultObject = null;
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
// 通过反射机制创建实例
resultObject = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = “”;
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true);// 打开javabean的访问private权限
field.set(resultObject, cols_value);
}
}
return resultObject;
}
/**
- 通过反射机制访问数据库
- @param
- @param sql
- @param params
- @param cls
- @return
- @throws Exception
*/
public List findMoreRefResult(String sql, List params,
Class cls) throws Exception {
List list = new ArrayList();
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
T resultObject = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = “”;
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true);
field.set(resultObject, cols_value);
}
list.add(resultObject);
}
return list;
}
public void releaseConn() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
在主函数中调用它:
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import jdbc.JdbcUtils;
import java.util.Scanner;
public class Main {
/**
* @param args
*/
public static void main(String[] args) {
System.out.println("请输入您的账号:");
// 判断是否还有输入
String str1;
Scanner scan = new Scanner(System.in);
str1 = scan.next();
JdbcUtils jdbc = new JdbcUtils();
jdbc.getConnection();
List<Object> list = new ArrayList<Object>();
list.add(str1);
try {
List<Map<String, Object>> ll;
ll = jdbc.findMoreResult("select * from 成绩表1 where s#=?", list);
if (ll.size() == 0) {
System.out.println("student not existed");
} else {
for (int i = 0; i < ll.size(); i++) {
String s1 = (String) ll.get(i).get("sname");
String s2 = (String) ll.get(i).get("cname");
Integer s3 = (Integer) ll.get(i).get("score");
String.valueOf(s1);
System.out.println("姓名 "+s1+"课程 " + s2 + " 成绩" + s3);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("search failed");
e.printStackTrace();
}
try {
Map<String, Object> map = jdbc.findSimpleResult(
"select * from 教师表 where t#=?", list);
if (map.size() == 0) {
System.out.println("teachers not existed");
} else {
boolean flag=true;
do{
System.out.println("老师您好!");
System.out.println("请输入你接下来的操作:");
System.out.println("1、添加");
System.out.println("2、修改");
System.out.println("3、删除");
System.out.println("4、查询");
int a = scan.nextInt();
switch(a){
case 1:
System.out.println("请依次输入学号 课程号 学生名 课程名 成绩:");
String str3;
str3 = scan.next();
String str4;
str4 = scan.next();
String str5;
str5 = scan.next();
String str6;
str6 = scan.next();
String str18;
str18 = scan.next();
try {
List<Object> lists = new ArrayList<Object>();
lists.add(str3);
lists.add(str4);
lists.add(str5);
lists.add(str6);
lists.add(str18);
if (jdbc.updateByPreparedStatement(
"insert into 成绩表1 values (?,?,?,?,?)", lists))
System.out.println("添加成功");
break;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
case 2:
System.out.println("请输入你想修改的学生的学号:");
String str7;
str7 = scan.next();
System.out.println("请输入你想修改的学生的课程号:");
String str77;
str77 = scan.next();
System.out.println("请输入你想修改的成绩:");
String str8;
str8 = scan.next();
try {
List<Object> lists2 = new ArrayList<Object>();
lists2.add(str8);
lists2.add(str7);
lists2.add(str77);
if (jdbc.updateByPreparedStatement(
"update 成绩表1 set score=? where s#=? and c#=?", lists2))
System.out.println("修改成功");
break;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
case 3:
System.out.println("请输入你想删除的学生的学号:");
String str9;
str9 = scan.next();
try {
List<Object> listss = new ArrayList<Object>();
listss.add(str9);
if (jdbc.updateByPreparedStatement(
"delete from 成绩表1 where s#=?", listss))
System.out.println("删除成功");
break;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
case 4:
System.out.println("请选择学号或课程号(课程号1 学号2):");
int a1 = scan.nextInt();
switch(a1){
case 1:
System.out.println("请输入课程号:");
String str11;
str11 = scan.next();
List<Object> list6 = new ArrayList<Object>();
list6.add(str11);
try{List<Map<String, Object>> ll3;
ll3 = jdbc
.findMoreResult("select * from 成绩表1 where c#=?", list6);
if (ll3.size() == 0) {
System.out.println("class not existed");
} else {
for (int i = 0; i < ll3.size(); i++) {
Integer s1 = (Integer) ll3.get(i).get("s#");
String s2 = (String) ll3.get(i).get("sname");
Integer s3 = (Integer) ll3.get(i).get("score");
String.valueOf(s1);
System.out.println("学号 " + s1 + " 姓名" + s2 +" 成绩"+ s3);
}
break;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("search failed");
e.printStackTrace();
}
case 2:
System.out.println("请输入学生学号:");
// 判断是否还有输入
String str10;
str10 = scan.next();
List<Object> list5 = new ArrayList<Object>();
list5.add(str10);
try {
List<Map<String, Object>> ll2;
ll2 = jdbc
.findMoreResult("select * from 成绩表1 where s#=?", list5);
if (ll2.size() == 0) {
System.out.println("student not existed");
} else {
for (int i = 0; i < ll2.size(); i++) {
Integer s1 = (Integer) ll2.get(i).get("c#");
String s2 = (String) ll2.get(i).get("cname");
String s3 = (String) ll2.get(i).get("sname");
Integer s4 = (Integer) ll2.get(i).get("score");
String.valueOf(s1);
System.out.println("课程号 " + s1 + " 课程名" + s2 +" 姓名"+s3+ "成绩" +s4);
}
break;
}
}catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("search failed");
e.printStackTrace();
}
}
}
System.out.println(“输入0返回菜单”);
int exit=scan.nextInt();
if(exit==0){flag=false;}
}while(!flag);
System.out.println(“你已退出系统”);
}
}catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println(“search failed”);
e.printStackTrace();
}
}
}
运行与测试:
学生表
教师表
课程表
成绩表
学生登陆
老师登录(特定账号4、5、6)
添加
数据库实现
修改
数据库实现
删除
数据库实现
查询选了这门课所有学生的成绩
查询这个学生的所有课程成绩