package com.silei.day1012;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class ConnDB {
public static Connection conn = null;
public static Statement stm = null;
public static ResultSet rs = null;
public static List list=null;
public static void main(String[] args) {
Student stu=new Student(100,"李雪芮","1234567");
stm=connDataBase();
InputData(stm,stu);
list=queryData(stm);
OutputData(list);
}
//向数据库添加数据
public static void InputData(Statement stm,Student stu){
int id=stu.getId();
String name=stu.getName();
String password=stu.getPassword();
try {
stm.executeUpdate("insert into student values("+null+",'"+name+"','"+password+"')");
System.out.println("成功添加一条数据");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//循环输出List中的学生记录
public static void OutputData(List list){
for(Student stu:list){
System.out.println("ID:"+stu.getId()+"学生的名字："+stu.getName()+"\t密码："+stu.getPassword());
}
}
//从数据库中查找数据，把查找到的数据添加到List中
@SuppressWarnings("finally")
public static List queryData(Statement stm) {
list=new ArrayList();
Student stu=null;
try {
rs = (ResultSet) stm.executeQuery("select * from student");
while (rs.next()) {
stu=new Student();
stu.setId(rs.getInt(1));
stu.setName(rs.getString(2));
stu.setPassword(rs.getString(3));
list.add(stu);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
rs.close();
stm.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
//连接数据库
public static Statement connDataBase(){
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:src/my.db");
stm = conn.createStatement();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return stm;
}
}
