jdbc操作mysql
本文讲述2点:
一. jdbc 操作 mysql 。(封装一个JdbcUtils.java类,实现数据库表的增删改查)
1. 建立数据库连接
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
2.用PrepareStatement执行sql语句
pstmt = connection.prepareStatement(sql);
3. 获得执行Sql结果(int result)或结果集合(ResultSet)
int result = pstmt.executeUpdate(); (增,删,改)
ResultSet resultSet = pstmt.executeQuery(); (查询)
// 获取所有列的信息
ResultSetMetaData metaData = resultSet.getMetaData();
二. 用Java反射机制返回JavaBean,List<JavaBean>
看JdbcUtils.java 中具体代码。
------------------------------------------------------------------------------------------------------------
程序思路:
用eclipse建立一个java 工程,访问mysql数据库。数据库名称:mydb,表格名称:userinfo. user表格有三个属性(id , username , pswd)
工程目录:
1 JdbcUtils.java --封装数据库操作的类
package com.jdbc.dbutils; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.jdbc.data.UserInfo; import com.mysql.jdbc.Driver; public class JdbcUtils { // 定义数据库的用户名 private final String USERNAME = "root"; // 定义数据库的密码 private final String PASSWORD = "123456"; // 定义数据库的驱动信息 private final String DRIVER = "com.mysql.jdbc.Driver"; // 定义访问数据库的地址 private final String URL = "jdbc:mysql://localhost:3306/mydb"; // 定义访问数据库的连接 private Connection connection; // 定义sql语句的执行对象 private PreparedStatement pstmt; // 定义查询返回的结果集合 private ResultSet resultSet; public JdbcUtils() { // TODO Auto-generated constructor stub try { Class.forName(DRIVER); System.out.println("注册驱动成功!!"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block System.out.println("注册驱动失败!!"); } } // 定义获得数据库的连接 public Connection getConnection() { try { connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (Exception e) { // TODO: handle exception System.out.println("Connection exception !"); } return connection; } /** * 完成对数据库标的增加删除和修改的操作 * * @param sql * @param params * @return * @throws SQLException */ public boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException { boolean flag = false; int result = -1;// 表示当用户执行增加删除和修改的操作影响的行数 int index = 1; // 表示 占位符 ,从1开始 pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); // 填充占位符 } } result = pstmt.executeUpdate(); flag = result > 0 ? true : false; return flag; } /** * 查询返回单条记录 * * @param sql * @param params * @return * @throws SQLException */ public Map<String, Object> findSimpleResult(String sql, List<Object> params) throws SQLException { Map<String, Object> map = new HashMap<String, Object>(); pstmt = connection.prepareStatement(sql); int index = 1; if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); // 返回查询结果 ResultSetMetaData metaData = pstmt.getMetaData(); // 获取 结果中,一行所有列的结果 int cols_len = metaData.getColumnCount(); // 获得列的总数 while (resultSet.next()) { for (int i = 0; i < cols_len; i++) { String col_name = metaData.getColumnName(i + 1); // 获得第i列的字段名称 Object col_value = resultSet.getObject(col_name);// 返回 第i列的内容值 if (col_value == null) { col_value = ""; } map.put(col_name, col_value); } } return map; } /** * 查询返回多条记录 * * @param sql * @param params * @return * @throws SQLException */ public List<Map<String, Object>> findMoreResult(String sql, List<Object> params) throws SQLException { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); pstmt = connection.prepareStatement(sql); int index = 1; // 表示占位符 if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); // 返回查询结果集合 ResultSetMetaData metaData = resultSet.getMetaData(); // 获得列的结果 while (resultSet.next()) { Map<String, Object> map = new HashMap<String, Object>(); int cols_len = metaData.getColumnCount(); // 获取总的列数 for (int i = 0; i < cols_len; i++) { String col_name = metaData.getColumnName(i + 1); // 获取第 i列的字段名称 // ,列计算从1开始 Object col_value = resultSet.getObject(col_name); // 获取第i列的内容值 if (col_value == null) { col_value = ""; } map.put(col_name, col_value); } list.add(map); } return list; } /** * 查询返回单个JavaBean(使用java反射机制) * * @param sql * @param params * @param cls * @return * @throws Exception */ public <T> T findSimpleRefResult(String sql, List<Object> params, Class<T> cls) throws Exception { T resultObject = null; int index = 1; // 占位符 pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); // 填充占位符 } } resultSet = pstmt.executeQuery(); // 获取查询结果 ResultSetMetaData metaData = resultSet.getMetaData(); // 获取列的信息 int cols_len = metaData.getColumnCount(); // 获取总的列数 while (resultSet.next()) { // 通过反射机制创建实例 resultObject = cls.newInstance(); // java反射机制 for (int i = 0; i < cols_len; i++) { String col_name = metaData.getColumnName(i + 1); // 获取第i列的名称 Object col_value = resultSet.getObject(col_name); // 获取第i列的值 if (col_value == null) { col_value = ""; } Field field = cls.getDeclaredField(col_name); field.setAccessible(true);// 打开 JavaBean的访问 private权限 field.set(resultObject, col_value); } } return resultObject; } /** 查询返回多个JavaBean(通过java反射机制) * @param sql * @param params * @param cls * @return * @throws Exception */ public <T> List<T> findMoreRefResult(String sql, List<Object> params, Class<T> cls) throws Exception { List<T> list = new ArrayList<T>(); int index = 1; //占位符 pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); // 返回查询结果集合 ResultSetMetaData metaData = resultSet.getMetaData(); // 返回列的信息 int cols_len = metaData.getColumnCount(); // 结果集中总的列数 while (resultSet.next()) { // 通过反射机制创建一个java实例 T resultObject = cls.newInstance(); for (int i = 0; i < cols_len; i++) { String col_name = metaData.getColumnName(i + 1); // 获得第i列的名称 Object col_value = resultSet.getObject(col_name); // 获得第i列的内容 if (col_value == null) { col_value = ""; } Field field = cls.getDeclaredField(col_name); field.setAccessible(true); // 打开JavaBean的访问private权限 field.set(resultObject, col_value); } list.add(resultObject); } return list; } /**关闭数据库访问 * @throws SQLException */ public void releaseConn() throws SQLException{ if (resultSet!=null) { resultSet.close(); } if (pstmt!=null) { pstmt.close(); } if (connection!=null) { connection.close(); } } }
2. UserInfo.java (用来验证java反射机制的JavaBean , 属性名称和数据表userinfo的字段完全一致)
package com.jdbc.data; import java.io.Serializable; public class UserInfo implements Serializable { /** * */ private static final long serialVersionUID = 1L; private int id; private String username; private String pswd; public UserInfo() { // TODO Auto-generated constructor stub } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPswd() { return pswd; } public void setPswd(String pswd) { this.pswd = pswd; } public static long getSerialversionuid() { return serialVersionUID; } @Override public String toString() { return "UserInfo [id=" + id + ", username=" + username + ", pswd=" + pswd + "]"; } }
3. Test_insert.java 测试添加
package com.jdbc.test; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.jdbc.dbutils.JdbcUtils; public class Test_Insert { /** * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { JdbcUtils jdbcUtils = new JdbcUtils(); jdbcUtils.getConnection(); //增加一条记录。新增一个用户信息 uername = "jack" , password = "admin" String sql = "insert into userinfo(username,pswd) values(?,?)"; List<Object> params = new ArrayList<Object>(); params.add("jack"); params.add("admin"); try { boolean flag = jdbcUtils.updateByPreparedStatement(sql, params); System.out.println("添加一条记录: "+flag); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ jdbcUtils.releaseConn(); } } }
运行结果:
(用同样的方法,再增加一条记录username="rose",pswd="123"),此时数据库共2条记录。如下图:
4. Test_FindMore.java (查询多条记录)
package com.jdbc.test; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import com.jdbc.dbutils.JdbcUtils; public class Test_FindMore { /** 返回多条记录 * @param args * @throws Exception */ public static void main(String[] args) throws Exception { // TODO Auto-generated method stub JdbcUtils jdbcUtils = new JdbcUtils(); jdbcUtils.getConnection(); String sql = "select * from userinfo"; try { List<Map<String, Object>> list = jdbcUtils.findMoreResult(sql, null); System.out.println(list); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ jdbcUtils.releaseConn(); } } }
运行结果:
5. Test_FindSimple.java (查询返回单条记录)
package com.jdbc.test; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import com.jdbc.dbutils.JdbcUtils; public class Test_FindSimple { /**查询返回一条记录 * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { // TODO Auto-generated method stub JdbcUtils jdbcUtils = new JdbcUtils(); jdbcUtils.getConnection(); String sql = "select * from userinfo where username = ?"; List<Object> params = new ArrayList<Object>(); params.add("rose"); try { Map<String, Object> map = jdbcUtils.findSimpleResult(sql, params); System.out.println(map); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ jdbcUtils.releaseConn(); } } }
运行结果:
6.Test_RefMore.java 查询返回List<JavaBean>(使用Java反射机制)
package com.jdbc.test; import java.sql.SQLException; import java.util.List; import java.util.Map; import com.jdbc.data.UserInfo; import com.jdbc.dbutils.JdbcUtils; public class Test_RefMore { /**返回List<JavaBean> (用Java反射机制) * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { // TODO Auto-generated method stub JdbcUtils jdbcUtils = new JdbcUtils(); jdbcUtils.getConnection(); String sql = "select * from userinfo"; try { List<UserInfo> list = jdbcUtils.findMoreRefResult(sql, null , UserInfo.class); System.out.println(list); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ jdbcUtils.releaseConn(); } } }
7. Test_RefSimple.java 查询返回单个JavaBean (使用Java反射机制)
package com.jdbc.test; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import com.jdbc.data.UserInfo; import com.jdbc.dbutils.JdbcUtils; public class Test_RefSimple { /**返回一个JavaBean (用Java反射机制) * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { // TODO Auto-generated method stub JdbcUtils jdbcUtils = new JdbcUtils(); jdbcUtils.getConnection(); String sql = "select * from userinfo where username = ?"; List<Object> params = new ArrayList<Object>(); params.add("rose"); try { UserInfo userInfo = jdbcUtils.findSimpleRefResult(sql, params, UserInfo.class); System.out.println(userInfo); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ jdbcUtils.releaseConn(); } } }
运行结果:
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。