1.读取每一行学生信息,装载到一个对应的Student对象,编写insert、delete、update语句,通过jdbc实现数据的增删改
2.查询籍贯属于黑龙江省的所有学生,并按照生日排序,查询结果导出为csv文件,格式与student_insert.txt相同。
实现代码如下:
主类:
import java.util.Scanner;
/**
*
* @author 张弛有度!
*/
public class Test {
public static void main(String[] args) {
try {
int choice = 0;
System.out.println("输入'1'执行增加数据操作");
System.out.println("输入'2'执行删除操作");
System.out.println("输入'3'执行更改操作");
System.out.println("输入'4'执行查找操作");
System.out.println("输入'5'执行导出表操作");
System.out.println("输入'0'退出程序操作");
Scanner in = new Scanner(System.in);
while (true) {
choice = in.nextInt();
switch (choice) {
case 1:
DataGather gather = new DataGather();
gather.loadFile();
break;
case 2:
Delete delete = new Delete();
delete.delete();
break;
case 3:
Update update = new Update();
update.update();
break;
case 4:
Find find = new Find();
find.select();
break;
case 5:
CSV csv = new CSV();
csv.CSV_Select();
break;
case 0:
System.exit(0);
}
}
} catch (Exception e) {
}
}
}
其他类:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertDB {
private static final String url = "jdbc:mysql://localhost:3306/new_schema1?useSSL=false";
private static final String driver = "com.mysql.jdbc.Driver";
private static final String user = "root";
private static final String password = "199801";
public Connection con = null;
public static Connection getCon() {
Connection con = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, user, password);
if (con != null) {
System.out.println("你已连接到数据库:" + con.getCatalog());
}
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
System.out.println("连接数据库失败!");
}
return con;
}
public boolean insertDB(String stu_id, String stu_name, String stu_sex, String stu_height, String stu_native_place, String stu_date) throws SQLException {
boolean flag = false;
Statement stm = null;
String sql = "insert into student values('" + stu_id + "','"
+ stu_name + "','" + stu_sex + "','" + stu_height + "','" + stu_native_place + "','" + stu_date + "')";
try {
con = getCon();
stm = con.createStatement();
int i = stm.executeUpdate(sql);
if (i > 0) {
flag = true;
System.out.println("插入数据成功!");
}
} catch (SQLException e) {
flag = false;
} finally {
close(null, stm, con);
}
return flag;
}
//关闭相关连接
public void close(ResultSet rs, Statement stm, Connection con) {
try {
if (rs != null) {
rs.close();
}
if (stm != null) {
stm.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
}
}
}
import java.io.RandomAccessFile;
import java.io.UnsupportedEncodingException;
public class DataGather {
public static final String openFileStyle = "r";
public static final String fieldLimitChar = ",";
public static final int fieldAllCount = 6;
private int count;
private String stu_id;
private String stu_name;
private String stu_sex;
private String stu_height;
private String stu_native_place;
private String stu_date;
public void loadFile() {
try {
RandomAccessFile raf = new RandomAccessFile("D:/abc/Student_insert.txt", openFileStyle);
String line_record = raf.readLine();
while (line_record != null) {
parseRecord(line_record);
line_record = raf.readLine();
}
System.out.println("共有合法的记录" + count + "条");
} catch (Exception e) {
}
}
private void parseRecord(String line_record) throws Exception {
String[] fields = line_record.split(fieldLimitChar);
if (fields.length == fieldAllCount) {
stu_id = tranStr(fields[0]);
stu_name = tranStr(fields[1]);
stu_sex = tranStr(fields[2]);
stu_height = tranStr(fields[3]);
stu_native_place = tranStr(fields[4]);
stu_date = tranStr(fields[5]);
System.out.println(stu_id + " " + stu_name + " " + stu_sex + " " + stu_height + " " + stu_native_place + " " + stu_date);
InsertDB db = new InsertDB();
db.insertDB(stu_id, stu_name, stu_sex, stu_height, stu_native_place, stu_date);
count++;
}
}
private String tranStr(String oldstr) throws UnsupportedEncodingException { //解决中文乱码的问题
String newstr = "";
try {
newstr = new String(oldstr.getBytes("ISO-8859-1"), "GBK");
} catch (Exception e) {
e.printStackTrace();
}
return newstr;
}
}
执行删除:
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author 张弛有度!
*/
public class Delete {
private static final String url = "jdbc:mysql://localhost:3306/new_schema1?useSSL=false";
private static final String driver = "com.mysql.jdbc.Driver";
private static final String user = "root";
private static final String password = "199801";
public Connection con = null;
int count = 0;
public void delete() {
Connection conn = getCon(); //获得连接
try {
BufferedReader br = new BufferedReader(new FileReader(("D:/abc/Student_delete.txt")));
String TestNumber = br.readLine();
while (TestNumber != null) {
count++;
String sql = "delete from new_schema1.student where stu_id =" + TestNumber;
Statement stmt = conn.createStatement();
int i = stmt.executeUpdate(sql);
if (i > 0) {
System.out.println("删除id为" + TestNumber + "的学生信息成功!");
}
String Number = br.readLine();
TestNumber = Number;
}
br.close();
} catch (IOException | NumberFormatException | SQLException e) {
e.printStackTrace();
}
}
public static Connection getCon() {
Connection con = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, user, password);
if (con != null) {
System.out.println("你已连接到数据库:" + con.getCatalog());
}
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
System.out.println("连接数据库失败!");
}
return con;
}
}
执行改信息操作:
import java.io.RandomAccessFile;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static test1.DataGather.fieldAllCount;
import static test1.DataGather.openFileStyle;
/**
*
* @author 张弛有度!
*/
public class Update {
private static final String url = "jdbc:mysql://localhost:3306/new_schema1?useSSL=false";
private static final String driver = "com.mysql.jdbc.Driver";
private static final String user = "root";
private static final String password = "199801";
private String File_id;
private String File_name;
private String File_sex;
private String File_height;
private String File_place;
private String File_date;
public Connection con = null;
public int database_id;
public String database_name;
public String database_sex;
public String database_height;
public String database_place;
public String database_date;
public static final String fieldLimitChar = ",";
public void update() {
try {
RandomAccessFile raf = new RandomAccessFile("D:/abc/Student_update.txt", openFileStyle);
String line_record = raf.readLine();
while (line_record != null) {
parseRecord(line_record);
line_record = raf.readLine();
System.out.println(File_id);
String sql = "select * from new_schema1.student where stu_id = " + File_id;
Statement stmt = null;
try {
con = getCon(); //获得连接
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
database_id = rs.getInt(1);//或者为rs.getString(1),根据数据库中列的值类型确定,参数为第一列
database_name = rs.getString(2);
database_sex = rs.getString(3);
database_height = rs.getString(4);
database_place = rs.getString(5);
database_date = rs.getString(6);
// System.out.println(database_id + " " + database_name + " " + database_sex + " " + database_height + " " + database_place + " " + database_date);
}
//可以将查找到的值写入类,然后返回相应的对象
} catch (SQLException e) {
e.printStackTrace();
}
int i = 0;
String sql1 = "update new_schema1.student set stu_id =?,stu_name=?,stu_sex=?,stu_height=?,stu_native_place=?,stu_date=? where stu_id = ?"; //注意要有where条件
PreparedStatement preStmt = null;
try {
preStmt = con.prepareStatement(sql1);
preStmt.setString(1, File_id); //(新)改为
if (!File_name.equals("")) {
preStmt.setString(2, File_name);
} else {
preStmt.setString(2, database_name);
}
if (!File_sex.equals("")) {
preStmt.setString(3, File_sex); //(新)改为
} else {
preStmt.setString(3, database_sex);
}
if (!File_height.equals("")) {
preStmt.setString(4, File_height); //(新)改为
} else {
preStmt.setString(4, database_height);
}
if (!File_place.equals("")) {
preStmt.setString(5, File_place); //(新)改为
} else {
preStmt.setString(5, database_place);
}
if (!File_date.equals("")) {
preStmt.setString(6, File_date); //(新)改为
} else {
preStmt.setString(6, database_date);
}
preStmt.setString(7, File_id); //主键的信息
i = preStmt.executeUpdate();
if (i > 0) {
System.out.println("更新数据操作执行成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close1(preStmt);
}
}
} catch (Exception e) {
e.printStackTrace();
}
//返回影响的行数,1为执行成功
}
//关闭相关连接
public void close1(PreparedStatement preStmt) {
try {
if (preStmt != null) {
preStmt.close();
System.out.println("关闭连接成功!");
}
} catch (SQLException e) {
}
}
public static Connection getCon() {
Connection con = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, user, password);
if (con != null) {
System.out.println("你已连接到数据库:" + con.getCatalog());
}
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
System.out.println("连接数据库失败!");
}
return con;
}
private void parseRecord(String line_record) throws Exception {
String[] fields = line_record.split(fieldLimitChar);
if (fields.length == fieldAllCount) {
File_id = tranStr(fields[0]);
File_name = tranStr(fields[1]);
File_sex = tranStr(fields[2]);
File_height = tranStr(fields[3]);
File_place = tranStr(fields[4]);
File_date = tranStr(fields[5]);
}
}
private String tranStr(String oldstr) throws UnsupportedEncodingException { //解决中文乱码的问题
String newstr = "";
try {
newstr = new String(oldstr.getBytes("ISO-8859-1"), "GBK");
} catch (Exception e) {
e.printStackTrace();
}
return newstr;
}
}
执行查找操作:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author 张弛有度!
*/
public class Find {
private static final String url = "jdbc:mysql://localhost:3306/new_schema1?useSSL=false";
private static final String driver = "com.mysql.jdbc.Driver";
private static final String user = "root";
private static final String password = "199801";
public void select() {
String sql = "select * from new_schema1.student where stu_id = 1614010830";
Connection con = getCon(); //获得连接
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
int m1_id = rs.getInt(1);//或者为rs.getString(1),根据数据库中列的值类型确定,参数为第一列
String m2_name = rs.getString(2);
String m3_sex = rs.getString(3);
String m4_height = rs.getString(4);
String m5_place = rs.getString(5);
String m6_date = rs.getString(6);
System.out.println("Id: " + m1_id);
System.out.println("Name: " + m2_name);
System.out.println("Sex: " + m3_sex);
System.out.println("Height: " + m4_height);
System.out.println("Place: " + m5_place);
System.out.println("Date: " + m6_date);
}
//可以将查找到的值写入类,然后返回相应的对象
} catch (SQLException e) {
e.printStackTrace();
}
}
public static Connection getCon() {
Connection con = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, user, password);
if (con != null) {
System.out.println("你已连接到数据库:" + con.getCatalog());
}
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
System.out.println("连接数据库失败!");
}
return con;
}
}
实现将指定数据导入到csv文件中:
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author 张弛有度!
*/
public class CSV {
private static final String url = "jdbc:mysql://localhost:3306/new_schema1?useSSL=false";
private static final String driver = "com.mysql.jdbc.Driver";
private static final String user = "root";
private static final String password = "199801";
public void CSV_Select() {
Connection con = getCon(); //获得连接
try {
String sql = "select * from new_schema1.student order by stu_date";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
Statement st1 = con.createStatement();
String sql1 = "select * from new_schema1.student where stu_id = " + rs.getString(1);
// System.out.println(rs.getString(1)); //测试点
ResultSet rs1 = st1.executeQuery(sql1);
while (rs1.next()) {
String id = rs1.getString(1);
String name = rs1.getString(2);
String sex = rs1.getString(3);
String height = rs1.getString(4);
String place = rs1.getString(5);
String date = rs1.getString(6);
// System.out.println(id + "," + name + "," + sex + "," + height + "," + place + "," + date); //测试点
String getSign = place.substring(0, 4);
if (getSign.equals("黑龙江省")) {
String message = id + "," + name + "," + sex + "," + height + "," + place + "," + date;
System.out.println(message); //测试点
try {
FileOutputStream fos = new FileOutputStream("D:/abc/b.csv", true);
fos.write(message.getBytes());
String sign = "\n";
fos.write(sign.getBytes());
System.out.println("数据导入文件成功!");
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("您数据库中地址信息过短");
}
}
public static Connection getCon() {
Connection con = null;
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, user, password);
if (con != null) {
System.out.println("你已连接到数据库:" + con.getCatalog());
}
} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
System.out.println("连接数据库失败!");
}
return con;
}
}
-----------------------------以上就是该练习的全部代码------------------------