JAVA-JDBC
包含以下几步:
1. 数据库连接: 加载数据库驱动Class.forName("com.mysql.jdbc.Driver"); ,获得数据连接conn=DriverManager.getConnection(URL, USER, PASSWORD);
2. 读取数据 : 建立执行对象,建立接收对象并接收执行语句的结果.
3. 关闭数据库
查询与其他操作不同
PreparedStatement 这个预处理类 可以将里面有?的替换掉
注意日期 java.sql.Date 是继承于 java.util.Date 注意用时候转换
连接数据库:
DBUtil
package MVCTest; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class DBUtil { private static String URL="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8"; private static String USER="root"; private static String PASSWORD=""; private static Connection conn; static { try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(URL, USER, PASSWORD); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static Connection getConnection(){ return conn; } }
数据库操作:
DAO
package MVCTest; import java.sql.Connection; import java.sql.Date; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.Statement; /* * 业务处理类 */ public class DAO { public void addUser(User u) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "insert into basic " + "(id,name,age,password,date)" + "values( ?,?,?,?,current_date())"; PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); ptmt.setString(1, u.getId()); ptmt.setString(2, u.getName()); ptmt.setInt(3, u.getAge()); ptmt.setString(4, u.getPassword()); ptmt.execute(); } //com.mysql.jdbc.JDBC4PreparedStatement@96b38e: update basic set name='xsy',age=22,password='1234',date='2015-04-09'where id='1' public void delUser(User u) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "delete from basic " + "where id=?"; PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); ptmt.setString(1,u.getId());//这里字符串的替代 ptmt.execute(); } public void updateUser(User u) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "update basic" + "set name=?,age=?,password=?,date=?" + "where id=?"; PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); ptmt.setString(1, u.getName()); ptmt.setInt(2, u.getAge()); ptmt.setString(3, u.getPassword()); ptmt.setDate(4, new Date(u.getDate().getTime())); //日期转换,这里要用java.sql.Date类 ptmt.setString(5, u.getId()); System.out.println(ptmt.toString());//可以看PreparedStatement的处理 ptmt.execute(); } public List<User> queryUser(String name) throws SQLException { User user = null; List<User> list = new ArrayList<User>(); Connection conn = DBUtil.getConnection(); String sql = "select * from basic where name=?"; PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); ptmt.setString(1, name); ResultSet re = ptmt.executeQuery(); while(re.next()){ user = new User(); user.setAge(re.getInt("age")); user.setId(re.getString("id")); user.setName(name); user.setDate(re.getDate("date")); user.setName(re.getString("name")); user.setPassword(re.getString("password")); list.add(user); } return list; } /* * 为了解决参数无限的情况,即按照某类需求查询 * 解决了不定参的问题 */ public List<User> queryUser(Map<String,Object> parms) throws SQLException { User user = null; List<User> list = new ArrayList<User>(); Connection conn = DBUtil.getConnection(); StringBuilder sql = new StringBuilder("select * from basic where 1=1 ");//这个技巧避免了and添加的分情况 if(parms!=null && parms.size()>0){ for(Map.Entry<String, Object> t:parms.entrySet()){ sql.append(" and " + t.getKey()+ " = "+t.getValue()); } } System.out.println(sql.toString()); PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql.toString()); ResultSet re = ptmt.executeQuery(); while(re.next()){ user = new User(); user.setAge(re.getInt("age")); user.setId(re.getString("id")); user.setName(re.getString("name")); user.setDate(re.getDate("date")); user.setName(re.getString("name")); user.setPassword(re.getString("password")); list.add(user); } User [] t = list.toArray(new User[0]); for(int i = 0;i<t.length;i++) System.out.println(t[i]); return list; } public User get(String id) throws SQLException{ User user =null; Connection conn = DBUtil.getConnection(); String sql = "select * from basic where id=?"; PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); ptmt.setString(1, id); ResultSet re = ptmt.executeQuery(); while(re.next()){ user = new User(); user.setAge(re.getInt("age")); user.setId(id); user.setDate(re.getDate("date")); user.setName(re.getString("name")); user.setPassword(re.getString("password")); } return user; } }
实例类:
package MVCTest; import java.sql.SQLException; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; public class User { private String name; private String password; private String id; public String getId() { return id; } public void setId(String id) { this.id = id; } private int age; private Date date; public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public int getAge() { return age; } @Override public String toString() { return "User [name=" + name + ", password=" + password + ", id=" + id + ", age=" + age + ", date=" + date + "]"; } public void setAge(int age) { this.age = age; } public static void main(String[] args) { DAO dao = new DAO(); System.out.println(Integer.valueOf("234")); System.out.println(Integer.parseInt("234")); try { Map<String,Object> parms = new HashMap<String,Object>(); parms.put("name", "'xsy'"); parms.put("id", "'1'");//这个添加要用单引号.list中取出来字符串放入没有单引号,不像PreparedStatement 会自己处理 dao.queryUser(parms); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。