Java与数据库学习总结

时间：2022-11-17 23:33:10

1、连接数据库

 package utils;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Statement;

 public class JDBCUtils {

     private JDBCUtils() {

     }

     private static Connection con;

     static {

         try {

             // 注册驱动

             Class.forName("com.mysql.jdbc.Driver");

             // 获得连接 对象

             String url = "jdbc:mysql://localhost:3306/day35";

             // 账号密码

             String username = "root";

             String password = "root";

             // 连接数据库

             con = DriverManager.getConnection(url, username, password);

         } catch (Exception e) {

             e.printStackTrace();

         }

     }

     // 定义静态方法 返回数据库的连接

     public static Connection getConnection() {

         return con;

     }

     // 关资源

     public static void close(Connection con, Statement stat) {

         if (stat != null) {

             try {

                 stat.close();

             } catch (SQLException e) {

                 e.printStackTrace();

             }

             if (con != null) {

                 try {

                     con.close();

                 } catch (SQLException e) {

                     e.printStackTrace();

                 }

             }

         }

     }

     public static void close(Connection con, Statement stat, ResultSet rs) {

         if (rs != null) {

             try {

                 rs.close();

             } catch (SQLException e) {

                 e.printStackTrace();

             }

             if (stat != null) {

                 try {

                     stat.close();

                 } catch (SQLException e) {

                     e.printStackTrace();

                 }

                 if (con != null) {

                     try {

                         con.close();

                     } catch (SQLException e) {

                         e.printStackTrace();

                     }

                 }

             }

         }

     }

 }

还可以通过配置文件方式连接数据库

配置文件(database.properties)：

 driverClass=com.mysql.jdbc.Driver

 url=jdbc:mysql://localhost:3306/day35

 username=root

 password=root

Java：

 package utils;

 import java.io.InputStream;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.util.Properties;

 public class JDBCUtilsConfig {

     private static Connection con;

     private static String driverClass;

     private static String url;

     private static String username;

     private static String password;

     static {

         try {

             readConfig();

             Class.forName(driverClass);

             con = DriverManager.getConnection(url, username, password);

         } catch (Exception e) {

             e.printStackTrace();

         }

     }

     // 读配置文件

     private static void readConfig() throws Exception {

         // 类加载器

         InputStream in = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("database.properties");

         Properties p = new Properties();

         p.load(in);

         driverClass = p.getProperty("driverClass");

         url = p.getProperty("url");

         username = p.getProperty("username");

         password = p.getProperty("password");

     }

     public static Connection getConnection() {

         return con;

     }

 }

2、访问数据库

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.SQLException;

 import java.sql.Statement;

 public class JDBCDemo {

     public static void main(String[] args) throws ClassNotFoundException, SQLException {

         Class.forName("com.mysql.jdbc.Driver");

         String url = "jdbc:mysql://localhost:3306/day35";

         String uname = "root";

         String pwd = "root";

         Connection con = DriverManager.getConnection(url, uname, pwd);

         //获得语句执行平台

         Statement sta = con.createStatement();

         sta.executeUpdate("insert into money (name) values ('we232')");

         sta.close();

         con.close();

     }

 }
 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Statement;

 //  查询

 public class JDBCDemo1 {

     public static void main(String[] args) throws ClassNotFoundException, SQLException {

         // 1 注册驱动

         Class.forName("com.mysql.jdbc.Driver");

         // 2获得连接 对象

         String url = "jdbc:mysql://localhost:3306/day35";

         String username = "root";

         String password = "root";

         Connection con = DriverManager.getConnection(url, username, password);

         // 3 获取执行sql语句对象

         Statement stat = con.createStatement();// 有注入攻击风险，不建议使用，可以用另一种平台

         // 4 调用执行者对象的方法

         String sql = "SELECT * FROM sort";

         ResultSet rs = stat.executeQuery(sql);

         // 5 rs结果集 ResultSet 方法 bollean next(); 返回true 有结果返回false 没有

         while (rs.next()) {

             // 获取每一列数据 ResultSet getxxx方法

             System.out.println(rs.getInt("sid") + "  " + rs.getDouble("sprice") + "   " + rs.getString("sdesc"));

         }

         // 5 关资源

         rs.close();

         stat.close();

         con.close();

     }

 }

另一种语句执行平台

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.SQLException;

 public class JDBCDemo2 {

     public static void main(String[] args) throws ClassNotFoundException, SQLException {

         // 1 注册驱动

         Class.forName("com.mysql.jdbc.Driver");

         // 2获得连接 对象

         String url = "jdbc:mysql://localhost:3306/day35";

         String username = "root";

         String password = "root";

         Connection con = DriverManager.getConnection(url, username, password);

         // 3 获得执行对象 换执行平台对象 prepareStatement

         String sql = "UPDATE sort SET sname=?,sprice=? WHERE sid=?";

         PreparedStatement pst = con.prepareStatement(sql);

         // pst 有方法 setxxx(占位符的位置，值)

         pst.setObject(1, "饭缸");

         pst.setObject(2, "20000");

         pst.setObject(3, 1);

         int s = pst.executeUpdate();

         System.out.println(s);

         // 4 关

         pst.close();

         con.close();

     }

 }

3、使用第三方工具包访问数据库

 import java.sql.Connection;

 import java.sql.SQLException;

 import org.apache.commons.dbutils.DbUtils;

 import org.apache.commons.dbutils.QueryRunner;

 public class Demo2 {

     private static Connection con = JDBCUtilsConfig.getConnection();

     public static void main(String[] args) throws SQLException {

         insert();

         update();

         delete();

     }

     public static void insert() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "insert into sort (sname,sprice,sdesc) values (?,?,?)";

         Object[] param = { "猫", 555, "小橘猫" };

         qr.update(con, sql, param);

         DbUtils.close(con);

     }

     public static void update() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "update sort set sname = ?,sprice = ?,sdesc = ? where id = ?";

         Object[] param = { "qy95", 40, "AAA", 6 };

         qr.update(con, sql, param);

         DbUtils.close(con);

     }

     public static void delete() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "delete from sort where id = ?";

         qr.update(con, sql, 9);

         DbUtils.close(con);

     }

 }

4、第三方包8种结果集处理方式

 package cn.zlh.Demo;

 import java.sql.Connection;

 import java.sql.SQLException;

 import java.util.List;

 import java.util.Map;

 import javax.management.Query;

 import org.apache.commons.dbutils.DbUtils;

 import org.apache.commons.dbutils.QueryRunner;

 import org.apache.commons.dbutils.handlers.ArrayHandler;

 import org.apache.commons.dbutils.handlers.ArrayListHandler;

 import org.apache.commons.dbutils.handlers.BeanHandler;

 import org.apache.commons.dbutils.handlers.BeanListHandler;

 import org.apache.commons.dbutils.handlers.ColumnListHandler;

 import org.apache.commons.dbutils.handlers.MapHandler;

 import org.apache.commons.dbutils.handlers.MapListHandler;

 import org.apache.commons.dbutils.handlers.ScalarHandler;

 import cn.zlh.domain.Sort;

 import cn.zlh.utils.JDBCUtilsConfig;

 // 8种结果集处理

 public class Demo3 {

     private static Connection con = JDBCUtilsConfig.getConnection();

     public static void main(String[] args) throws SQLException {

         arrayHandler();

         arrayListHandler();

         beanHandler();

         beanListHandler();

         columnListHandler();

         scalarHandler();

         mapHandler();

         mapListHandler();

     }

     // 将每一条数据都以key-value的形式放在Map集合中，再把这些Map放到一个List集合中

     public static void mapListHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select * from sort";

         List<Map<String, Object>> query = qr.query(con, sql, new MapListHandler());

         DbUtils.close(con);

         for (Map<String, Object> map : query) {

             for (String key : map.keySet()) {

                 System.out.print(key + ":" + map.get(key));

             }

             System.out.println();

         }

     }

     // 将第一天数据以key-value的形式放在Map集合中

     public static void mapHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select * from sort";

         Map<String, Object> query = qr.query(con, sql, new MapHandler());

         DbUtils.close(con);

         for (String key : query.keySet()) {

             System.out.println(key + ":" + query.get(key));

         }

     }

     // 可以使用聚合函数统计数据

     public static void scalarHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select count(*) from sort";

         Long query = qr.query(con, sql, new ScalarHandler<Long>());

         DbUtils.close(con);

         System.out.println(query);

     }

     // 将所有数据中某一列的值放到一个List集合中

     public static void columnListHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select * from sort";

         List<Object> query = qr.query(con, sql, new ColumnListHandler<Object>("sprice"));

         DbUtils.close(con);

         for (Object o : query) {

             System.out.println(o);

         }

     }

     // 将每一条数据各自封装成一个对象，再把这些对象放到一个List集合里

     public static void beanListHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select * from sort";

         List<Sort> query = qr.query(con, sql, new BeanListHandler<Sort>(Sort.class));

         DbUtils.close(con);

         for (Sort s : query) {

             System.out.println(s);

         }

     }

     // 将第一条数据封装成一个对象

     public static void beanHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select * from sort";

         Sort query = qr.query(con, sql, new BeanHandler<>(Sort.class));

         DbUtils.close(con);

         System.out.println(query);

     }

     // 将每一条数据各自放到一个数组里，再把这些数组放到一个List集合里

     public static void arrayListHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select * from sort";

         List<Object[]> query = qr.query(con, sql, new ArrayListHandler());

         DbUtils.close(con);

         for (Object[] o : query) {

             for (Object obj : o) {

                 System.out.print(obj + ",");

             }

             System.out.println();

         }

     }

     // 将第一条数据放到数组里

     public static void arrayHandler() throws SQLException {

         QueryRunner qr = new QueryRunner();

         String sql = "select * from sort";

         Object[] query = qr.query(con, sql, new ArrayHandler());

         DbUtils.close(con);

         for (Object o : query) {

             System.out.println(o);

         }

     }

 }

相关文章