一、写SQL语句
1、创建student数据表包含 id、name姓名、tel电话、sex性别字段
CREATE DATABASE schooljava;
USE schooljava;
CREATE TABLE student (
id INT(11),
name VARCHAR(25),
tel INT(11),
sex VARCHAR(6)
);
DESC student;
2、往student表中加10条数据
INSERT INTO student VALUES
-> (1,'张三',1234567890,'男'),
-> (2,'李黑',1564481133,'男'),
-> (3,'小明',1215541841,'男')
-> (4,'老六',1234567890,'男'),
-> (5,'小辉',1395482655,'男');
-> (6,'小红',1595262000,'女'),
-> (7,'王女',1825523665,'女');
-> (8,'小兰',1234567890,'女');
-> (9,'小刘',1234567890,'男'),
-> (10,'小郑',1234567890,'男');
二、写Java代码(要求只用PreparedStatement对象,变化的值都用?代替)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.cj.jdbc.Driver;
public class schooljava {
Connection connection;
public static void main(String[] args) throws SQLException {
schooljava studentsql=new schooljava();
studentsql.getConnection();
System.out.println("查询所有学生数据,结果为:");
studentsql.selectAll();
System.out.println("添加三条学生数据,结果为:");
studentsql.add(11,"嘿嘿","133586246","男");
studentsql.add(12,"黑黑","139565655","男");
studentsql.add(13,"红红","133656859","女");
studentsql.selectAll();
System.out.println("查询中电话以182开头的所有学生信息,结果为:");
studentsql.selectPart182();
System.out.println("修改第5条数据,结果为:");
studentsql.update5(5,"131242353");
studentsql.selectAll();
System.out.println("查询男性所有学生信息,结果为:");
studentsql.selectMan();
System.out.println("修改第10条数据,结果为:");
studentsql.update10(10,"大帅哥");
studentsql.selectAll();
System.out.println("查询中电话以139开头的所有学生信息,结果为:");
studentsql.selectPart139();
System.out.println("删除第3条数据,结果为:");
studentsql.delete(3);
studentsql.selectAll();
System.out.println("查询女性所有学生信息,结果为:");
studentsql.selectWoman();
System.out.println("查询所有学生数据,结果为:");
studentsql.selectAll();
studentsql.close();
}
//查询女性所有学生信息
public void selectWoman() throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("select*from student where sex='女';");
//执行SQL语句返回结果集
ResultSet resultSet=preparedStatement.executeQuery();
//遍历结果集
while(resultSet.next()) {
System.out.print(resultSet.getInt("id")+" ");
System.out.print(resultSet.getString("name")+" ");
System.out.print(resultSet.getString("tel")+" ");
System.out.println(resultSet.getString("sex")+" ");
}
//关闭连接释放资源
resultSet.close();
preparedStatement.close();
}
//删除student表中第3条数据
public void delete(int id) throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("delete from student where id= ?;");
preparedStatement.setInt(1,id);
//执行SQL语句返回结果集
preparedStatement.executeUpdate();
}
//查询中电话以139开头的所有学生信息
public void selectPart139() throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("select*from student where tel like '139%';");
//执行SQL语句返回结果集
ResultSet resultSet=preparedStatement.executeQuery();
//遍历结果集
while(resultSet.next()) {
System.out.print(resultSet.getInt("id")+" ");
System.out.print(resultSet.getString("name")+" ");
System.out.print(resultSet.getString("tel")+" ");
System.out.println(resultSet.getString("sex")+" ");
}
//关闭连接释放资源
resultSet.close();
preparedStatement.close();
}
//修改student表中第10条数据,姓名改为“大帅哥”
public void update10(int id, String name) throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("update student set name= ? where id= ?;");
preparedStatement.setString(1, name);
preparedStatement.setInt(2, id);
//获取statement对象
preparedStatement.executeUpdate();
}
//查询所有男性的学生信息
public void selectMan() throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("select*from student where sex='男';");
//执行SQL语句返回结果集
ResultSet resultSet=preparedStatement.executeQuery();
//遍历结果集
while(resultSet.next()) {
System.out.print(resultSet.getInt("id")+" ");
System.out.print(resultSet.getString("name")+" ");
System.out.print(resultSet.getString("tel")+" ");
System.out.println(resultSet.getString("sex")+" ");
}
//关闭连接释放资源
resultSet.close();
preparedStatement.close();
}
//修改第5条数据
public void update5(int id,String tel) throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("update student set tel= ? where id= ?;");
preparedStatement.setString(1, tel);
preparedStatement.setInt(2, id);
//获取statement对象
preparedStatement.executeUpdate();
}
//查询电话以182开头的所有学生信息
public void selectPart182() throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("select*from student where tel like '182%';");
//执行SQL语句返回结果集
ResultSet resultSet=preparedStatement.executeQuery();
//遍历结果集
while(resultSet.next()) {
System.out.print(resultSet.getInt("id")+" ");
System.out.print(resultSet.getString("name")+" ");
System.out.print(resultSet.getString("tel")+" ");
System.out.println(resultSet.getString("sex")+" ");
}
//关闭连接释放资源
resultSet.close();
preparedStatement.close();
}
//添加课程
public void add(int id,String name,String tel,String sex) throws SQLException {
//获取statement对象
PreparedStatement preparedStatement=connection.prepareStatement("insert into student value(?,?,?,?);");
preparedStatement.setInt(1,id);
preparedStatement.setString(2,name);
preparedStatement.setString(3,tel);
preparedStatement.setString(4,sex);
//执行SQL语句返回结果集
preparedStatement.executeUpdate();
}
public void getConnection() throws SQLException {
//第一步:注册驱动
DriverManager.registerDriver(new Driver());
//第二步:获取连接
connection=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/school_java","root","1234");
}
//查询所有学生数据
public void selectAll() throws SQLException {
//第三步:获取statement对象
PreparedStatement preparedStatement=
connection.prepareStatement("select*from student;");
//第四步:执行SQL语句返回结果集
ResultSet resultSet=preparedStatement.executeQuery();
//第五步:遍历结果集
while(resultSet.next()) {
System.out.print(resultSet.getInt("id")+" ");
System.out.print(resultSet.getString("name")+" ");
System.out.print(resultSet.getLong("tel")+" ");
System.out.println(resultSet.getString("sex")+" ");
}
//第六步:关闭连接释放资源
resultSet.close();
preparedStatement.close();
}
public void close()throws SQLException {
connection.close();
}
}
运行结果如下:
三、查询课程
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Driver;
public class JDBCDemo {
Connection connection;
public static void main(String[] args) throws SQLException {
JDBCDemo mysqlDemo = new JDBCDemo();
mysqlDemo.getConnection();
System.out.println("查询所有课程,结果为:");
mysqlDemo.selectAll();
System.out.println("新增4号课程名为WEB");
mysqlDemo.add(4,"WEB");
System.out.println("查询所有课程,结果为:");
mysqlDemo.selectAll();
System.out.println("修改3号课程,改为PhotoShop");
mysqlDemo.update(3,"PhotoShop");
System.out.println("查询所有课程,结果为:");
mysqlDemo.selectAll();
System.out.println("删除4号课程");
mysqlDemo.delete(4);
System.out.println("查询所有课程,结果为:");
mysqlDemo.selectAll();
mysqlDemo.close();
}
public void delete(int id) throws SQLException {
// 第三步: 获取statement对象
PreparedStatement prepareStatement =
connection.prepareStatement
("delete from course where id = ?;");
prepareStatement.setInt(1, id);
// 第四步: 执行SOL语句
prepareStatement.executeUpdate();
}
public void update(int id, String name) {
}
public void add(int id, String name) throws SQLException {
// 第三步:获取statement对象
PreparedStatement prepareStatement =
connection.prepareStatement
("insert into course values (?,?);");
prepareStatement.setInt(1, id);
prepareStatement.setString(2, name);
}
//注册驱动并获取链接的方法
public void getConnection() throws SQLException {
// 第一步:注册驱动
DriverManager.registerDriver(new Driver());
// 第二步:获取连接
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/school_java","root","1234");
}
//查询表course中的所有数据
public void selectAll() throws SQLException{
// 第三步:获取statement对象
PreparedStatement prepareStatement = connection.prepareStatement("select * from course;");
// 第四步: 执行SQL语句返回结果集
ResultSet resultSet = prepareStatement.executeQuery();
// 第五步:遍历结果集
while(resultSet.next()) {
System.out.print(resultSet.getInt("id"));
System.out.println(resultSet.getString("name"));
}
// 第六步:关闭连接释放资源
resultSet.close();
prepareStatement.close();
}
//关闭链接
public void close() throws SQLException{
connection.close();
}
}
运行结果为: