public class DBHelper {//定义四个属性: username, password, url, driverclassname private static final String USERNAME = "TEST"; //final ,变量不能改,不能重写,不能继承,变量名要大写 private static final String PASSWORD = "TEST"; private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl"; private static final String DIVERCLASSNAME = "oracle.jdbc.OracleDriver"; // Connection接口是jdk中定义好的一个数据库连接的接口 public static Connection getConnection () { //返回型 Connection cn = null; try { Class.forName(DIVERCLASSNAME); //类加载器去加载数据库的驱动程序 cn = DriverManager.getConnection(URL, USERNAME, PASSWORD); //DriverManager:驱动程序管理类, 由这个类来获取Connection接口的实例 //直接调用DriverManager的静态方法getConnection()获取连接 } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return cn; } public static void main(String[] args) { //目的是用来测试数据库是否引入成功,可以不用来写 Connection c = getConnection (); System.out.println(c); destroy(c,null,null); } public static void destroy ( Connection c , Statement sm , ResultSet r) { //释放资源 if (c != null) { try { c.close(); } catch (SQLException e) { e.printStackTrace(); } c = null; } if (sm != null) { try { sm.close(); } catch (SQLException e) { e.printStackTrace(); } sm = null; } if (r != null) { try { r.close(); } catch (SQLException e) { e.printStackTrace(); } r = null; } } }
以上是连接数据库
以下是从数据库里取的连接之后进行增.删.改.查的 操作
public class Student { //将数据库里面的内容分装成一个类,就是一个数据模型 private Integer id; private String name; private String code; private Integer sex; private Date birth; public Student() { super(); } public Student(Integer id, String name, String code, Integer sex, Date birth) { super(); this.id = id; this.name = name; this.code = code; this.sex = sex; this.birth = birth; } public Student(String name, String code, Integer sex, Date birth) { //如果ID不用传参数,采取序列生成 super(); this.name = name; this.code = code; this.sex = sex; this.birth = birth; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } public Integer getSex() { return sex; } public void setSex(Integer sex) { this.sex = sex; } public Date getBirth() { return birth; } public String setTime() { //为了获取日期的格式而构造的方法 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); return sdf.format(this.birth); } public void setBirth(Date birth) { this.birth = birth; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", code=" + code + ", sex=" + sex + ", birth=" + birth + "]"; } }
public class DatabaceMethodDal { //dal结尾的包为一个处理数据的包,test结尾的包为一个测试是否添加进数据的包, //until结尾的包为一个工具的包 private Connection coon; private Statement sm; private PreparedStatement ps; private ResultSet rs; //添加一条数据 public int insertstudent () { init(); //调用那个初始化的方法 String sql = "insert into student st" + " values(sq_test.nextval,'张华','stu-1070','1',sysdate,'LAU-100','2')"; //nexttval为序列生成 int num = -1; try { num = sm.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } close(); return num; } //更新数据,用参数来实现,解决SQL注入的问题,防止后面的参数被随意更改 public int UpdateStudent (Integer id, String name) { String sql = "update student s set s.birthday = sysdate " + "where s.id = ? and s.name = ?"; init(sql); int p = -1; try { ps.setInt(1, id); //占位符索引值, 第几个 ? , 就写几, 从1开始数 ps.setString(2, name); p = ps.executeUpdate(); //用于增删改的语句 } catch (SQLException e) { e.printStackTrace(); } close();//关闭资源 return p; } //批量处理 添加数据,直接通过Statement来实现的 public int[] InsertStudent (List<Student> list) { int[] num = null; init(); try { for (Student st : list) { init(); String sql = "insert into student values (sq_test.nextval,'"+st.getName() +"','"+st.getCode()+"','"+st.getSex()+"',to_date('"+st.setTime()+"','yyyy-mm-dd'))"; sm.addBatch(sql); //prepareStatement.addBatch()-->每次处理一条sql语句 } num = sm.executeBatch(); //返回int[] prepareStatement.executeBatch()-->将一批命令提交给数据库去执行 } catch (SQLException e) { e.printStackTrace(); } close(); return num; } //批量处理 插入数据,直接通过prepareStatement public int[] InsertStudentByprepare (List<Student> list) { String sql = "insert into AppUser values(sq_test,?,?,?,?)"; init(sql); int[] num = null; try { for (Student s : list) { ps.setString(1,s.getName()); ps.setString(2, s.getCode()); ps.setInt(3, s.getSex()); ps.setDate(4, new java.sql.Date(s.getBirth().getTime())); ps.addBatch(); } num = ps.executeBatch(); } catch (SQLException e) { e.printStackTrace(); } close(); return num; } //查询 public List<Student> selectStudent (Integer id,String name) { //返回一个集合 String sql = "select * from student st where st.id = ? and st.name = ?"; init(sql); List<Student> list = null; list = new ArrayList<Student> (); try { ps.setInt(1, id ); ps.setString(2, name); rs= ps.executeQuery(); //查询,返回一个由Statement执行查询sql之后返回的结果集 while (rs.next()) { //查询一条,相当于迭代器里面的指针 Student st = new Student(rs.getInt("id"), //里面写的是数据库里列的字段名 rs.getString("name"),rs.getString("code"), rs.getInt("sex"),rs.getDate("birthday")); list.add(st); } } catch (SQLException e) { e.printStackTrace(); } close(); return list; } public void init () { //初始化,连接数据库 coon = DBHelper.getConnection(); try { sm = coon.createStatement(); } catch (SQLException e) { e.printStackTrace(); } } public void init ( String sql) { //重载方法 coon = DBHelper.getConnection(); try { ps = coon.prepareStatement(sql); //连接的是sql } catch (SQLException e) { e.printStackTrace(); } } public void close() { //关闭资源的方法 DBHelper.destroy(coon, sm, rs); } }
在Tomcat上打印一下,看看有没有在数据库里面对数据处理成功
public class TestDemo { //测试是否修改数据成功 public static void main(String[] args) { DatabaceMethodDal dm = new DatabaceMethodDal(); //写入数据 int i = dm.insertstudent(); System.out.println(i); //更新数据 Student s = new Student(); int i = dm.UpdateStudent(23, "张华"); System.out.println(i); //批量处理 通过Statement来实现的 List<Student> list = new ArrayList<Student>(); Student st1 = new Student("王小明","stu-1025",1,new Date()); Student st2 = new Student("王小花","stu-1026",2,new Date()); list.add(st1); list.add(st2); //int [] arr = dm.InsertStudent(list); int[] arr2 = dm.InsertStudentByprepare(list); System.out.println(Arrays.toString(arr2)); //查询 List<Student> list = dm.selectStudent(1, "陈迪"); System.out.println(list); } }