package day04; import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.Scanner; import java.sql.*; import com.tarena.util.*; /* *从控制台读取信息,写入到文件中 *从文件中读取信息,写入到数据库中 *从数据库中读取信息,计算 */ public class Demo3 { private static List<Student> list; public static void main(String[] args) { Demo3 demo3 = new Demo3(); try { // 将数据写入文件中 demo3.readConsoleAndWriteFile(); // 从文件中读取信息到list链表中 demo3.getScoreFromFile(); // 获取数据库连接 Connection con = JDBCUtil.getConnection(); Statement statement = con.createStatement();// 从list链表中读取数据存放到数据库中 String sql = "insert into Student(s_id,s_name,s_score) values(?,?,?)"; PreparedStatement pre = con.prepareStatement(sql); pre.clearBatch(); for (int i = 1; i <= list.size(); i++) { pre.setInt(1, i); pre.setString(2, list.get(i - 1).getS_name()); pre.setDouble(3, list.get(i - 1).getS_score()); pre.addBatch(); } pre.executeBatch(); // 从数据库中读取信息并计算 String sql1 = "select * from Student"; ResultSet rSet = statement.executeQuery(sql1); while (rSet.next()) { Student student = new Student(rSet.getString(2), rSet.getDouble(3)); list.add(student); } String maxName = list.get(0).getS_name(); double maxScore = list.get(0).getS_score(); String minName = list.get(0).getS_name(); double minScore = list.get(0).getS_score();; double sum = 0; for (int i = 0; i < list.size(); i++) { if (maxScore < list.get(i).getS_score()) { maxScore = list.get(i).getS_score(); maxName = list.get(i).getS_name(); } if (minScore > list.get(i).getS_score()) { minScore = list.get(i).getS_score(); minName = list.get(i).getS_name(); } sum += list.get(i).getS_score(); } System.out.println("平均成绩为:" + sum / list.size()); System.out.println("最高成绩为" + maxName + " " + maxScore); System.out.println("最低成绩为" + minName + " " + minScore); rSet.close(); pre.close(); con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void readConsoleAndWriteFile() { Scanner scanner = new Scanner(System.in); PrintWriter pw = null; try { pw = new PrintWriter("C:\\demo.txt"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } while (true) { System.out.println("请输入学生的姓名和成绩:格式如 张三:100"); String nameandscore = scanner.next(); if (nameandscore.equalsIgnoreCase("bye")) { // equalsIgnoreCase不区分大小写 pw.close(); break; } pw.println(nameandscore); // pw.append(nameandscore+'\n'); pw.flush(); } return; } public void getScoreFromFile() { BufferedReader br = null; try { br = new BufferedReader(new FileReader("C:\\demo.txt")); list = new ArrayList<Student>(); String s = null; while ((s = br.readLine()) != null) { String[] s1 = s.split(":"); Student student = new Student(s1[0], Double.parseDouble(s1[1])); list.add(student); } } catch (Exception e) { // TODO: handle exception } finally { try { br.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
package day04; public class Student { private String s_name; private double s_score; public Student(){ //无参构造 } public Student(String s_name,double s_score){ this.s_name= s_name; this.s_score = s_score; } //生成set/get方法 右键-source-Generate getters and setters public String getS_name() { return s_name; } public void setS_name(String s_name) { this.s_name = s_name; } public double getS_score() { return s_score; } public void setS_score(double s_score) { this.s_score = s_score; } }
忘了说明,其实这里的数据库插入数据可以
create table student(s_id int primary key auto_increment; ….)
插入数据时可以让其自动增长id 不用自己插入的
package com.tarena.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import day04.JDBChelloworld; public class JDBCUtil { public static final String USERNAME = "root"; public static final String PWD="tarena"; public static final String MTSQLURL ="jdbc:mysql://localhost:3306/t_tarena"; public static Connection getConnection(){ Connection connection=null; try { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //建立连接 connection = DriverManager.getConnection(MTSQLURL,USERNAME,PWD); //System.out.println(connection); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); }catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return connection; } public static void main(String[] args) { JDBChelloworld jdbc = new JDBChelloworld(); try { jdbc.getConnection(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } }