CREATE DATABASE schooljava;
USE schooljava;
CREATE TABLE student (
id INT(11),
name VARCHAR(25),
tel INT(11),
sex VARCHAR(6)
);
DESC student;
INSERT INTO student VALUES
-> (1,'张三',1234567890,'男'),
-> (2,'李四',1234567890,'男'),
-> (3,'王五',1234567890,'男'),
-> (4,'老六',1234567890,'男'),
-> (5,'小明',1234567890,'男'),
-> (6,'小红',1234567890,'女'),
-> (7,'小玲',1234567890,'女'),
-> (8,'小兰',1234567890,'女'),
-> (9,'小刘',1234567890,'男'),
-> (10,'小郑',1234567890,'男');
package Mysql;
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 schooljava=new schooljava();
schooljava.getConnection();
schooljava.selectAll();
schooljava.add(11,"亚索",1824567890,"男");
schooljava.add(12,"锐雯",1394567890,"女");
schooljava.add(13,"盖伦",1893154635,"男");
schooljava.select182();
schooljava.xiugai();
schooljava.chanan();
schooljava.dsg();
schooljava.select139();
schooljava.sc();
schooljava.chanv();
schooljava.selectAll();
}
public void add(int id, String name,int tel,String sex) throws SQLException {
//第三步:获取statement对象
PreparedStatement preparedStatement=connection.prepareStatement("insert into student values(?,?,?,?);");
//括号是第几个问号,后面是设置id
preparedStatement.setInt(1, id);
preparedStatement.setString(2, name);
preparedStatement.setInt(3,tel);
preparedStatement.setString(4,sex);
preparedStatement.executeUpdate();
//第四步:执行sql语句
}
//第一步:注册驱动、获取链接
public void getConnection()throws SQLException {
//第一步:注册驱动
DriverManager.registerDriver(new Driver());
//第二步:获取连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school","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.getInt("tel"));
System.out.println(resultSet.getString("sex"));
//第六步:关闭连接释放资源
}resultSet.close();
preparedStatement.close();}
//第六步:关闭连接释放资源
public void select182() throws SQLException {
PreparedStatement preparedStatement=connection.prepareStatement("select*from student where tel like '182%'");
ResultSet resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
System.out.print(resultSet.getInt("id"));
System.out.print(resultSet.getString("name"));
System.out.print(resultSet.getInt("tel"));
System.out.println(resultSet.getString("sex"));}
}
public void xiugai() throws SQLException {
PreparedStatement preparedStatement=connection.prepareStatement("update student set tel=1312423530 where id=5");
preparedStatement.executeUpdate( );
}
public void chanan() throws SQLException {
PreparedStatement preparedStatement=connection.prepareStatement("select*from student where sex='男'");
ResultSet resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
System.out.print(resultSet.getInt("id"));
System.out.print(resultSet.getString("name"));
System.out.print(resultSet.getInt("tel"));
System.out.println(resultSet.getString("sex"));}
}
public void dsg() throws SQLException {
PreparedStatement preparedStatement=connection.prepareStatement("update student set name='大帅哥' where id=10");
preparedStatement.executeUpdate( );
}
public void select139() throws SQLException {
PreparedStatement preparedStatement=connection.prepareStatement("select*from student where tel like '139%'");
ResultSet resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
System.out.print(resultSet.getInt("id"));
System.out.print(resultSet.getString("name"));
System.out.print(resultSet.getInt("tel"));
System.out.println(resultSet.getString("sex"));}
}
public void sc() throws SQLException {
PreparedStatement preparedStatement=connection.prepareStatement("delete from student where id=3");
preparedStatement.executeUpdate( );
}
public void chanv() throws SQLException {
PreparedStatement preparedStatement=connection.prepareStatement("select*from student where sex='女'");
ResultSet resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
System.out.print(resultSet.getInt("id"));
System.out.print(resultSet.getString("name"));
System.out.print(resultSet.getInt("tel"));
System.out.println(resultSet.getString("sex"));}}
}