题目:
已知MySQL数据库school,字符集为utf8。在其中建立表student,并向表里插入几条记录。
create table student(
id int PRIMARY KEY auto_increment,
name varchar(20) not null,
score float not null);
insert into student values(null,'李丽',86);
insert into student values(null,'王五',99);
insert into student values(null,'张三',88);
已知数据库连接字符串为:
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/school?useunicode=true&characterEncoding=utf-8",
"root",
"123456"); //数据库服务器名称(地址)、端口号、数据库名称、用户名、密码须根据实际情况改变
功能要求:
(1)向表中增加记录并显示所有记录(数据自己指定);
(2)从表中删除id=1的记录,并显示所有记录;
(3)修改表中记录:查询条件id=2,将name修改为:山东理工,修改完毕显示所有记录;
(4)查询表中id=3的记录并显示。
提交说明: 粘贴JAVA程序代码。
代码:
PreparedStatement
import java.sql.*;
public class Main {
public static void main(String[] args) throws SQLException {
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/school",
"root",
"123456"); //数据库服务器名称(地址)、端口号、数据库名称、用户名、密码须根据实际情况改变
//增加记录
String sql1="insert into student values(null,'Mini',86)";
String sql2="insert into student values(null,'Kate',87)";
PreparedStatement pst1=conn.prepareStatement(sql1);
PreparedStatement pst2=conn.prepareStatement(sql2);
pst1.executeUpdate();
pst2.executeUpdate();
//显示所有记录
String sql="select * from student";
PreparedStatement pst=conn.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while (rs.next())
{
int id=rs.getInt(1);
String name=rs.getString(2);
int score=rs.getInt(3);
System.out.println(id + " " + name + " " + score);
}
//删除id=1的记录
String sql3="delete from student where id=1";
PreparedStatement pst3=conn.prepareStatement(sql3);
pst3.executeUpdate();
//显示所有记录
while (rs.next())
{
int id=rs.getInt(1);
String name=rs.getString(2);
int score=rs.getInt(3);
System.out.println(id + " " + name + " " + score);
}
//修改id=2,将name改为”山东理工”
String sql4="update student set name='山东理工' where id=2";
PreparedStatement pst4=conn.prepareStatement(sql4);
pst4.executeUpdate();
//显示所有记录
while (rs.next())
{
int id=rs.getInt(1);
String name=rs.getString(2);
int score=rs.getInt(3);
System.out.println(id + " " + name + " " + score);
}
//查询id=3的记录并显示
String sql5="select * from student where id=3";
PreparedStatement pst5=conn.prepareStatement(sql5);
rs=pst5.executeQuery();
if(rs.next())
{
int id=rs.getInt(1);
String name=rs.getString(2);
int score=rs.getInt(3);
System.out.println(id + " " + name + " " + score);
}
}
}