使用SQL Server数据库或者MySQL数据库各自的客户端工具，完成如下任务：
（1）创建数据库students；
（2）在数据students中创建表scores，包括如下字段：学号、姓名、性别、得分，字段类型自行定义。学号为主键。
接着使用JDBC编写Java程序，完成如下任务：
（1）在表格scores插入5条记录，代表5个学生，三位男生二位女生，各字段内容自定（“得分”字段填入自定分数）；
（2）显示5位学生的所有信息；
（3）将三位男生的得分减去5分，将两位女生的成绩加上3分；
（4）从键盘输入不同学号，根据学号显示相应学生的所有信息。
import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.TextArea;
import java.sql.*;
import java.util.*;
import javax.swing.*;
public class Student_Score {
public static void MySQL()throws SQLException {
Connection conn=null;
Statement stmt = null;
ResultSet rs = null;
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String ur1="jdbc:sqlserver://localhost:1433;databaseName = Student";
String usename = "sa";
String password = "123456";
conn=DriverManager.getConnection(ur1,usename,password);
stmt = conn.createStatement();
}catch (Exception e) {
// TODO: handle exception
}
//----------------------------------------
// insert(stmt);
delete(stmt);
// update(stmt);
select(stmt,rs);
// Select_JAVA(stmt,rs);
//------------------------------------
if(rs!=null) {rs.close();}
if(stmt!=null) {stmt.close();}
if(conn!=null) {conn.close();}
}
public static void select(Statement stmt,ResultSet rs)throws SQLException {
rs=stmt.executeQuery("select * from score");
System.out.println("学号 姓名 性别 成绩");
while(rs.next())
{
int id = rs.getInt("学号");
String name = rs.getString("姓名");
String sex = rs.getString("性别");
int grade = rs.getInt("得分");
System.out.println(id+" "+name+" "+sex+" "+grade);
}
}
public static void insert(Statement stmt)throws SQLException {
Object insert=stmt.execute("insert into score values(1,'学生1','男',90)");
insert=stmt.execute("insert into score values(2,'学生2','男',97)");
insert=stmt.execute("insert into score values(3,'学生3','男',100)");
insert=stmt.execute("insert into score values(4,'学生4','女',100)");
insert=stmt.execute("insert into score values(5,'学生5','女',91)");
System.out.println("have insert");
}
public static void update(Statement stmt)throws SQLException {
Object update=stmt.execute("update score\r\n"+"set 得分=得分-5\r\n" + "where 性别='男'");
update=stmt.execute("update score\r\n"+"set 得分=得分+3\r\n" + "where 性别='女'");
}
public static void Select_JAVA(Statement stmt,ResultSet rs)throws SQLException {
//-----
System.out.println("输入要查询学生学号");
//-------
int i;
Scanner sc = new Scanner(System.in);
i=sc.nextInt();
//---------
if(i==1)
rs=stmt.executeQuery("select * from score where 学号=1");
if(i==2)
rs=stmt.executeQuery("select * from score where 学号=2");
if(i==3)
rs=stmt.executeQuery("select * from score where 学号=3");
if(i==4)
rs=stmt.executeQuery("select * from score where 学号=4");
if(i==5)
rs=stmt.executeQuery("select * from score where 学号=5");
System.out.println("学号 姓名 性别 成绩");
while(rs.next())
{
int id = rs.getInt("学号");
String name = rs.getString("姓名");
String sex = rs.getString("性别");
int grade = rs.getInt("得分");
System.out.println(id+" "+name+" "+sex+" "+grade);
}
}
public static void delete(Statement stmt)throws SQLException {
Object delete=stmt.execute("delete from score\r\n" + "where 学号=1");
delete=stmt.execute("delete from score\r\n" + "where 学号=2");
delete=stmt.execute("delete from score\r\n" + "where 学号=3");
delete=stmt.execute("delete from score\r\n" + "where 学号=4");
delete=stmt.execute("delete from score\r\n" + "where 学号=5");
delete=stmt.execute("delete from score");
}
//==================上面JAVA实验====下面SQL实验（本人java and SQLserver均为优秀可供参考）=======================================================================
public static void createAndShowGUI() {
//---------------------------------------------------
JFrame f = new JFrame("XX的窗口(SQL实验版)");
f.setLayout(new BorderLayout());
f.setSize(600,600);
f.setLocation(300,200);
f.setVisible(true);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JTextArea tf = new JTextArea(12,34);
// JScrollPane scrollpane = new JScrollPane(tf);
tf.setEditable(false);
f.add(tf);
JTextField inputField =new JTextField(20);
JButton btn= new JButton("导入数据");
JButton btn1= new JButton("修改数据");
JButton btn2= new JButton("查询全部数据");
JButton btn3= new JButton("查询部分数据");
JButton btn4= new JButton("删除数据");
tf.setFont(new Font("宋体", Font.PLAIN, 20));
inputField.setFont(new Font("宋体", Font.PLAIN, 20));
JPanel panel = new JPanel();
panel.add(btn);
panel.add(btn1);
panel.add(btn2);
panel.add(btn3);
panel.add(btn4);
// f.add(scrollpane,BorderLayout.PAGE_START);
f.add(panel,BorderLayout.PAGE_END);
JScrollPane scrollPane = new JScrollPane(tf);
scrollPane.setHorizontalScrollBarPolicy(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
scrollPane.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED);
f.add(scrollPane);
//-------------
btn.addActionListener(e->{
try {
SQL_miaomiao(tf,0);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
});
btn1.addActionListener(e->{
try {
SQL_miaomiao(tf,1);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
});
btn2.addActionListener(e->{
try {
SQL_miaomiao(tf,2);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
});
btn3.addActionListener(e->{
try {
SQL_miaomiao(tf,3);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
});
btn4.addActionListener(e->{
try {
SQL_miaomiao(tf,4);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
});
}
public static void SQL_miaomiao(JTextArea tf,int n)throws SQLException{
Connection conn=null;
Statement stmt = null;
ResultSet rs = null;
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String ur1="jdbc:sqlserver://localhost:1433;databaseName = Student";
String usename = "sa";
String password = "123456";
conn=DriverManager.getConnection(ur1,usename,password);
stmt = conn.createStatement();
}catch (Exception e) {
// TODO: handle exception
}
if(n==0)
{
insert(stmt);
selectSQL_1(stmt,rs,tf);
}
if(n==1)
{
update(stmt);
tf.append("修改后为：\n\r");
selectSQL_1(stmt,rs,tf);
}
if(n==2)
{
selectSQL_1(stmt,rs,tf);
}
if(n==3)
{
selectSQL_2(stmt,rs,tf);
}
if(n==4)
{
delete(stmt);
selectSQL_1(stmt,rs,tf);
}
}
public static void selectSQL_1(Statement stmt,ResultSet rs,JTextArea tf)throws SQLException {
rs=stmt.executeQuery("select * from score");
tf.append("学号 姓名 性别 成绩\n\r");
while(rs.next())
{
int id = rs.getInt("学号");
String name = rs.getString("姓名");
String sex = rs.getString("性别");
int grade = rs.getInt("得分");
tf.append(id+" "+name+" "+sex+" "+grade+"\n\r");
}
}
public static void selectSQL_2(Statement stmt,ResultSet rs,JTextArea tf)throws SQLException {
JFrame f = new JFrame("XX的窗口(SQL实验版)");
f.setLayout(new FlowLayout(FlowLayout.LEFT,20,60));
f.setSize(300,220);
f.setLocation(400,400);
f.setVisible(true);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JTextField inputField = new JTextField(5);
JPanel panel = new JPanel();
JLabel label = new JLabel("输入学号");
panel.add(label);
panel.add(inputField);
JButton btn= new JButton("确定");
f.add(label);
f.add(inputField);
f.add(btn);
btn.addActionListener(e->{
String s=inputField.getText();
int i;
i=Integer.parseInt(s);
System.out.println(i);
try {
writeit_SQL(i,stmt,rs,tf);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
f.setVisible(false);
});
//-----------
}
public static void writeit_SQL (int i,Statement stmt,ResultSet rs,JTextArea tf) throws SQLException {
if(i==1)
rs=stmt.executeQuery("select * from score where 学号=1");
if(i==2)
rs=stmt.executeQuery("select * from score where 学号=2");
if(i==3)
rs=stmt.executeQuery("select * from score where 学号=3");
if(i==4)
rs=stmt.executeQuery("select * from score where 学号=4");
if(i==5)
rs=stmt.executeQuery("select * from score where 学号=5");
tf.append("学号 姓名 性别 成绩\n\r");
while(rs.next())
{
int id = rs.getInt("学号");
String name = rs.getString("姓名");
String sex = rs.getString("性别");
int grade = rs.getInt("得分");
tf.append(id+" "+name+" "+sex+" "+grade+"\n\r");
}
}
public static void main(String[] args)throws Exception {
// MySQL();
SwingUtilities.invokeLater(Student_Score::createAndShowGUI);
}
}
版权声明：本文为weixin_43700565原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。