写SQL语句
创建school_java数据库
创建student数据表包含 id、name姓名、tel电话、sex性别字段
往student表中加10条数据
写Java代码
要求只用PreparedStatement对象,变化的值都用?代替
package com.mysql.cj.jdbc;
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 Driver {
Connection connection;
public static void main(String[] args) throws SQLException {
Driver student= new Driver();
student.getConnection();
student.selectA11();
student.add(11, "一号", "12345678901", "男");
student.add(12, "二号", "12345678902", "女");
student.add(13, "三号", "12345678903", "男");
student.selectA11();
student.selectmll182();
student.upd(5, "13124235300");
student.selectA11();
student.selectnan();
student.upm(10, "大帅哥");
student.selectA11();
student.selectmll139();
student.delete(3);
student.selectA11();
student.selectnv();
student.selectA11();
student.close();
}
//创建连接
public void getConnection() throws SQLException {
DriverManager.registerDriver((java.sql.Driver) new Driver());
connection=DriverManager.getConnection
("jdbc:mysql://localhost:3306/school_java", "root", "1234");
}
//查询
public void selectA11() throws SQLException {
System.out.println("查询结果如下:");
PreparedStatement preparedStatement=
connection.prepareStatement("select * from student;");
ResultSet resultSet=preparedStatement.executeQuery();
while(resultSet.next()) {
System.out.println(resultSet.getInt("id")+" "+
resultSet.getString("name")+" "+
resultSet.getString("tel")+" "+
resultSet.getString("sex"));
}
resultSet.close();
preparedStatement.close();
}
public void selectnan() throws SQLException {
System.out.println("查询结果如下:");
PreparedStatement preparedStatement=
connection.prepareStatement("select * from student where sex='男';");
ResultSet resultSet=preparedStatement.executeQuery();
while(resultSet.next()) {
System.out.println(resultSet.getInt("id")+" "+
resultSet.getString("name")+" "+
resultSet.getString("tel")+" "+
resultSet.getString("sex"));
}
resultSet.close();
preparedStatement.close();
}
public void selectnv() throws SQLException {
System.out.println("查询结果如下:");
PreparedStatement preparedStatement=
connection.prepareStatement("select * from student where sex='女';");
ResultSet resultSet=preparedStatement.executeQuery();
while(resultSet.next()) {
System.out.println(resultSet.getInt("id")+" "+
resultSet.getString("name")+" "+
resultSet.getString("tel")+" "+
resultSet.getString("sex"));
}
resultSet.close();
preparedStatement.close();
}
//模糊查询
public void selectmll182() throws SQLException {
System.out.println("查询结果如下:");
PreparedStatement preparedStatement2=
connection.prepareStatement("select * from student where tel like '182%';");
ResultSet resultSet2=preparedStatement2.executeQuery();
while(resultSet2.next()) {
System.out.println(resultSet2.getInt("id")+" "+
resultSet2.getString("name")+" "+
resultSet2.getString("tel")+" "+
resultSet2.getString("sex"));
}
resultSet2.close();
preparedStatement2.close();
}
public void selectmll139() throws SQLException {
System.out.println("查询结果如下:");
PreparedStatement preparedStatement2=
connection.prepareStatement("select * from student where tel like '139%';");
ResultSet resultSet2=preparedStatement2.executeQuery();
while(resultSet2.next()) {
System.out.println(resultSet2.getInt("id")+" "+
resultSet2.getString("name")+" "+
resultSet2.getString("tel")+" "+
resultSet2.getString("sex"));
}
resultSet2.close();
preparedStatement2.close();
}
//修改
private void upd(int id, String tel) throws SQLException {
System.out.println("修改结果如下:");
PreparedStatement preparedStatement=
connection.prepareStatement("update student set tel = ? where id = ?");
preparedStatement.setString(1, tel);
preparedStatement.setInt(2, id);
preparedStatement.executeUpdate();
}
private void upm(int id, String name) throws SQLException {
System.out.println("修改结果如下:");
PreparedStatement preparedStatement=
connection.prepareStatement("update student set name = ? where id = ?");
preparedStatement.setString(1, name);
preparedStatement.setInt(2, id);
preparedStatement.executeUpdate();
}
//增加
public void add(int id,String name,String tel,String sex) throws SQLException {
System.out.println("增加结果如下:");
PreparedStatement preparedStatement=
connection.prepareStatement("insert into student values(?,?,?,?);");
preparedStatement.setInt(1,id);
preparedStatement.setString(2,name);
preparedStatement.setString(3,tel);
preparedStatement.setString(4,sex);
preparedStatement.executeUpdate();
}
//删除
private void delete(int id) throws SQLException {
PreparedStatement preparedStatement=
connection.prepareStatement("delete from student where id = ?;");
preparedStatement.setInt(1, id);
preparedStatement.executeUpdate();
System.out.println("已删除第"+id+"条数据");
}
//关闭
private void close() throws SQLException {
connection.close();
}
}
查询student表中所有学生信息
student表中新增三条数据
查询student表中电话182开头的所有学生信息
修改student表中第5条数据,电话改为“13124235300”
查询student表中所有男性的学生信息
修改student表中第10条数据,姓名改为“大帅哥”
查询student表中电话139开头的所有学生信息
删除student表中第3条数据
查询student表中所有女性的学生信息
查询student表中所有学生信息