Java通过JDBC连接MySQL数据库
代码描述：把前台获取的字段作为查询条件，返回符合条件的记录。
1 package com.imooc.dao; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.util.ArrayList; 9 import java.util.List; 10 11 import com.imooc.bean.Message; 12 13 /** 14 * 处理与数据库相关的查询操作 15 * 16 */ 17 public class MessageDao { 18 public List<Message> queryMessageList(String command,String description){ 19 List<Message> messageList = new ArrayList<Message>(); 20 try { 21 Class.forName("com.mysql.jdbc.Driver"); 22 Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/micro_message", "root", "admin"); 23 // StringBuilder速度快适合单线程，StringBuffer适合多线程操作大量数据，String每次都是生成新对象，需要垃圾回收。 24 StringBuilder sb = new StringBuilder("select ID,COMMAND,DESCRIPTION,CONTENT from message where 1=1 "); 25 // String sql = "select ID,COMMAND,DESCRIPTION,CONTENT from message"; 26 List<String> paramList = new ArrayList<String>(); 27 if(null != command && !"".equals(command)){ 28 sb.append(" and COMMAND=?"); 29 paramList.add(command); 30 } 31 if(null != description && !"".equals(description)){ 32 // MySQL中用%表示任意字符，用空格用于连接 33 sb.append(" and DESCRIPTION like '%' ? '%'"); 34 paramList.add(description); 35 } 36 PreparedStatement pstmt = conn.prepareStatement(sb.toString()); 37 for(int i=0;i<paramList.size();i++){ 38 // 给PreparedStatement中预执行SQL语句追加值 39 pstmt.setString(i+1, paramList.get(i)); 40 } 41 ResultSet rs = pstmt.executeQuery(); 42 while(rs.next()){ 43 Message message = new Message(); 44 messageList.add(message);// 这里messageList是个容器，只是对message的引用，所以可以提前放入容器中 45 message.setId(rs.getString("ID")); 46 message.setCommand(rs.getString("COMMAND")); 47 message.setDescription(rs.getString("DESCRIPTION")); 48 message.setContent(rs.getString("CONTENT")); 49 } 50 }catch (ClassNotFoundException e) { 51 e.printStackTrace(); 52 }catch (SQLException e) { 53 e.printStackTrace(); 54 } 55 return messageList; 56 } 57 58 } 59
