本文共 22070 字,大约阅读时间需要 73 分钟。
1、在IDEA里创建Maven项目
1.1、点击Create New Project
1.2、选择Maven,JDK这里用的是1.8,点击Next
1.3、填入“组织名”、“项目名”,版本是默认的,点击Next
1.4、选择创建路径,点击Finsh
1.5、这是创建完成的界面
2、用Maven连接Mysql的JDBC驱动
2.1、打开src下的pom.xml文件, 在里面添加Mysql的jdbc包的引用,代码如下
12 3 7mysql 4mysql-connector-java 58.0.18 6
2.2、添加完成后,IDEA右下角会出现下图提示,点击提示中的Import Changes,Maven就会开始下载资源
2.3、下载时页面左下角出现正在下载的提示
2.4、下载完成变成绿勾
3、连接数据库第一种方式:直接注册驱动,向数据库插入数据(不推荐使用)
3.1、在src——main——java目录下,新建一个LinkDatabaseInsert的类
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.PreparedStatement; 4 import java.sql.SQLException; 5 6 public class LinkDatabaseInsert { 7 public static void main(String[] args) throws ClassNotFoundException, SQLException { 8 //1.注册数据库的驱动 9 Class.forName("com.mysql.cj.jdbc.Driver");10 //2.获取数据库连接(里面内容依次是:"jdbc:mysql://主机名:端口号/数据库名","用户名","登录密码")11 Connection connection = DriverManager.getConnection("jdbc:mysql://rm-uf6lgkv4fd9776rxego.mysql.rds.aliyuncs.com:3306/study?serverTimezone=UTC&useSSL=false","root","whmilyY123!");12 //3.需要执行的sql语句(?是占位符,代表一个参数)13 String sql = "insert into stu(id,name,age) values(?,?,?)";14 //4.获取预处理对象,并依次给参数赋值15 PreparedStatement statement = connection.prepareCall(sql);16 statement.setInt(1,12); //数据库字段类型是int,就是setInt;1代表第一个参数17 statement.setString(2,"小明"); //数据库字段类型是String,就是setString;2代表第二个参数18 statement.setInt(3,16); //数据库字段类型是int,就是setInt;3代表第三个参数19 //5.执行sql语句(执行了几条记录,就返回几)20 int i = statement.executeUpdate();21 System.out.println(i);22 //6.关闭jdbc连接23 statement.close();24 connection.close();25 }26 }
3.2、运行程序,返回1,说明插入成功
4、连接数据库第二种方式——新建数据库配置文件,获取配置文件信息后,再注册数据库驱动
4.1、利用反射获取新建的数据库配置文件db.properties里的信息
4.1.1、在src——main——resources目录下,新建db.properties文件
1 driver=com.mysql.jdbc.Driver2 url=jdbc:mysql://rm-uf6lg6rxego.mysql.rds.aliyuncs.com:3306/study3 user=root4 password=wY123!
4.1.2、新建util包,然后在里面创建JdbcUtil类,利用反射获取db.properties文件信息,最后返回数据库连接
1 package util; 2 3 import java.io.InputStream; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.util.Properties; 7 8 //获取到db.properties文件中的数据库信息 9 public class JdbcUtil {10 //私有变量11 private static String driver;12 private static String url;13 private static String user;14 private static String password;15 16 //静态块17 static{18 try{19 //1.新建属性集对象20 Properties properties = new Properties();21 //2通过反射,新建字符输入流,读取db.properties文件22 InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");23 //3.将输入流中读取到的属性,加载到properties属性集对象中24 properties.load(input);25 //4.根据键,获取properties中对应的值26 driver = properties.getProperty("driver");27 url = properties.getProperty("url");28 user = properties.getProperty("user");29 password = properties.getProperty("password");30 }catch(Exception e){31 e.printStackTrace();32 }33 }34 35 //返回数据库连接36 public static Connection getConnection(){37 try{38 //注册数据库的驱动39 Class.forName(driver);40 //获取数据库连接(里面内容依次是:主机名和端口、用户名、密码)41 Connection connection = DriverManager.getConnection(url,user,password);42 //返回数据库连接43 return connection;44 }catch (Exception e){45 e.printStackTrace();46 }47 return null;48 }49 }
4.1.3、在java目录下创建LinkMysql类,调用JdbcUtil类返回的数据库连接操作数据库
1 import util.JdbcUtil; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.SQLException; 6 7 public class LinkMysql { 8 public static void main(String[] args) throws ClassNotFoundException, SQLException { 9 //获取数据库连接10 Connection connection = JdbcUtil.getConnection();11 //需要执行的sql语句12 String sql = "insert into stu(id,name,age) values(?,?,?)";13 //获取预处理对象,并给参数赋值14 PreparedStatement statement = connection.prepareCall(sql);15 statement.setInt(1,14);16 statement.setString(2,"李四");17 statement.setInt(3,16);18 //执行sql语句(插入了几条记录,就返回几)19 int i = statement.executeUpdate(); //executeUpdate:执行并更新20 System.out.println(i);21 //关闭jdbc连接22 statement.close();23 connection.close();24 }25 }
4.2、通过ResourceBundle类获取新建的数据库配置文件db.properties里的信息
4.2.1、在util包里面创建创建JdbcUtil2类,ResourceBundle类获取db.properties文件信息,最后返回数据库连接
1 package util; 2 3 import java.sql.*; 4 import java.util.ResourceBundle; 5 6 public class JdbcUtil2 { 7 //私有变量 8 private static String driver; 9 private static String url;10 private static String user;11 private static String password;12 13 //静态块14 static{15 try{16 //2.3通过ResourceBundle类拿到数据库连接信息17 ResourceBundle resourceBundle = ResourceBundle.getBundle("db");18 driver = resourceBundle.getString("driver");19 url = resourceBundle.getString("url");20 user = resourceBundle.getString("user");21 password = resourceBundle.getString("password");22 }catch(Exception e){23 e.printStackTrace();24 }25 }26 27 //返回数据库连接28 public static Connection getConnection(){29 try{30 //注册数据库的驱动31 Class.forName(driver);32 //获取数据库连接(里面内容依次是:主机名和端口、用户名、密码)33 Connection connection = DriverManager.getConnection(url,user,password);34 //返回数据库连接35 return connection;36 }catch (Exception e){37 e.printStackTrace();38 }39 return null;40 }41 42 //关闭结果集43 public static void closeResultSet(ResultSet resultSet) {44 if (resultSet != null) {45 try {46 resultSet.close();47 } catch (SQLException e) {48 e.printStackTrace();49 }50 }51 }52 53 //关闭预处理对象54 public static void closeStatement(Statement statement) {55 if (statement != null) {56 try {57 statement.close();58 } catch (SQLException e) {59 e.printStackTrace();60 }61 }62 }63 64 //关闭数据库连接65 public static void closeConnection(Connection connection){66 if(connection != null){67 try {68 connection.close();69 } catch (SQLException e) {70 e.printStackTrace();71 }72 }73 }74 75 //一次性关闭上面三个76 public static void closeResource(ResultSet resultSet,Statement statement,Connection connection){77 closeResultSet(resultSet);78 closeStatement(statement);79 closeConnection(connection);80 }81 }
4.2.2、在java目录下创建LinkMysql2类,调用JdbcUtil2类返回的数据库连接操作数据库
1 import util.JdbcUtil2; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.SQLException; 6 7 public class LinkMysql2 { 8 public static void main(String[] args) throws ClassNotFoundException, SQLException { 9 //获取数据库连接10 Connection connection = JdbcUtil2.getConnection();11 //需要执行的sql语句12 String sql = "insert into stu(id,name,age) values(?,?,?)";13 //获取预处理对象,并给参数赋值14 PreparedStatement statement = connection.prepareCall(sql);15 statement.setInt(1,19);16 statement.setString(2,"王五");17 statement.setInt(3,16);18 //执行sql语句(执行了几条记录,就返回几)19 int i = statement.executeUpdate(); //executeUpdate:执行并更新20 System.out.println(i);21 //关闭jdbc连接22 JdbcUtil2.closeResource(null,statement,connection);23 }24 }25 import util.JdbcUtil2;26 27 import java.sql.Connection;28 import java.sql.PreparedStatement;29 import java.sql.SQLException;30 31 public class LinkMysql2 {32 public static void main(String[] args) throws ClassNotFoundException, SQLException {33 //获取数据库连接34 Connection connection = JdbcUtil2.getConnection();35 //需要执行的sql语句36 String sql = "insert into stu(id,name,age) values(?,?,?)";37 //获取预处理对象,并给参数赋值38 PreparedStatement statement = connection.prepareCall(sql);39 statement.setInt(1,19);40 statement.setString(2,"王五");41 statement.setInt(3,16);42 //执行sql语句(执行了几条记录,就返回几)43 int i = statement.executeUpdate(); //executeUpdate:执行并更新44 System.out.println(i);45 //关闭jdbc连接46 JdbcUtil2.closeResource(null,statement,connection);47 }48 }
5、连接数据库的第三种方式:新建数据库配置文件,获取配置文件信息后,再通过DButils工具包连接数据库
5.1、打开src下的pom.xml文件, 在里面添加DButils的引用,代码如下
12 commons-dbutils 3commons-dbutils 41.5 5
5.2、创建DbutilsInsert类,往数据库内插入数据,代码如下
1 import org.apache.commons.dbutils.QueryRunner; 2 import util.JdbcUtil2; 3 4 import java.sql.Connection; 5 import java.sql.SQLException; 6 7 public class DbutilsInsert { 8 public static void main(String[] args) throws SQLException { 9 //创建dbUtils里面的QueryRunner对象10 QueryRunner queryRunner = new QueryRunner();11 //sql语句12 String sql = "insert into stu(id,name,age) values(?,?,?)";13 //存参数值的数组14 Object[] objects = {20,"小红",11};15 //获取数据库连接16 Connection connection = JdbcUtil2.getConnection();17 //执行sql语句,并返回影响的行数18 int i = queryRunner.update(connection,sql,objects);19 System.out.println(i);20 //关闭数据库连接21 connection.close();22 }23 }
5.3、创建DbutiUpdate类,更新数据库内数据,代码如下
1 import org.apache.commons.dbutils.QueryRunner; 2 import util.JdbcUtil2; 3 4 import java.sql.Connection; 5 import java.sql.SQLException; 6 7 public class DbutilsUpdate { 8 public static void main(String[] args) throws SQLException { 9 //创建dbUtils里面的QueryRunner对象10 QueryRunner queryRunner = new QueryRunner();11 //sql语句12 String sql = "update stu set name=? where id=?";13 //存参数值的数组14 Object[] objects = {"红红",21};15 //获取数据库连接16 Connection connection = JdbcUtil2.getConnection();17 //执行sql语句,并返回影响的行数18 int i = queryRunner.update(connection,sql,objects);19 System.out.println(i);20 //关闭数据库连接21 connection.close();22 }23 }
5.4、创建DbutiDelete类,删除数据库内数据,代码如下
1 import org.apache.commons.dbutils.QueryRunner; 2 import util.JdbcUtil2; 3 4 import java.sql.Connection; 5 import java.sql.SQLException; 6 7 public class DbutilsDelete { 8 public static void main(String[] args) throws SQLException { 9 //创建dbUtils里面的QueryRunner对象10 QueryRunner queryRunner = new QueryRunner();11 //sql语句12 String sql = "delete from stu where id=?";13 //存参数值的数组14 Object[] objects = {11};15 //获取数据库连接16 Connection connection = JdbcUtil2.getConnection();17 //执行sql语句,并返回影响的行数18 int i = queryRunner.update(connection,sql,objects);19 System.out.println(i);20 //关闭数据库连接21 connection.close();22 }23 }
5.5、创建UtilsSelectArrayHandler类,查询数据(ArrayHandler()只会返回第一条记录)
1 import org.apache.commons.dbutils.QueryRunner; 2 import org.apache.commons.dbutils.handlers.ArrayHandler; 3 import util.JdbcUtil2; 4 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 import java.util.Arrays; 8 9 public class UtilsSelectArrayHandler {10 public static void main(String[] args) throws SQLException {11 //创建dbUtils里面的QueryRunner对象12 QueryRunner queryRunner = new QueryRunner();13 //sql语句14 String sql = "select * from stu where age>?";15 //获取数据库连接16 Connection connection = JdbcUtil2.getConnection();17 //存参数值的数组18 Object[] params = {16};19 //执行查询,并以数组的形式返回查询结果(new ArrayHandler()只会返回第一条记录)20 Object[] objects = queryRunner.query(connection,sql, new ArrayHandler(),params);21 System.out.println(Arrays.toString(objects));22 System.out.println("\n");23 //关闭数据库连接24 connection.close();25 }26 }
5.6、创建UtilsSelectArrayListHandler类,查询数据(ArrayListHandler()会返回所有查询到的记录)
1 import org.apache.commons.dbutils.QueryRunner; 2 import org.apache.commons.dbutils.handlers.ArrayListHandler; 3 import util.JdbcUtil2; 4 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 import java.util.Arrays; 8 import java.util.List; 9 10 public class UtilsSelectArrayListHandler {11 public static void main(String[] args) throws SQLException {12 //创建dbUtils里面的QueryRunner对象13 QueryRunner queryRunner = new QueryRunner();14 //sql语句15 String sql = "select * from stu where age>?";16 //获取数据库连接17 Connection connection = JdbcUtil2.getConnection();18 //存参数值的数组19 Object[] params = {16};20 //执行查询,并以数组的形式返回查询结果(new ArrayListHandler()返回所有查询到的记录)21 List
5.7、创建UtilsSelectBeanHandler类,查询数据,并将查询数据转成实际对象(BeanHandler只会返回第一条记录)
先创建student类1 public class Student { 2 private String id; 3 private String name; 4 private int age; 5 private String tname; 6 7 public String getId() { 8 return id; 9 }10 11 public void setId(String id) {12 this.id = id;13 }14 15 public String getName() {16 return name;17 }18 19 public void setName(String name) {20 this.name = name;21 }22 23 public int getAge() {24 return age;25 }26 27 public void setAge(int age) {28 this.age = age;29 }30 31 public String getTname() {32 return tname;33 }34 35 public void setTname(String tname) {36 this.tname = tname;37 }38 39 @Override40 public String toString() {41 return "Student{" +42 "id='" + id + '\'' +43 ", name='" + name + '\'' +44 ", age=" + age +45 ", tname='" + tname + '\'' +46 '}';47 }48 }
再创建UtilsSelectBeanHandler类
import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import util.JdbcUtil2;import java.sql.Connection;import java.sql.SQLException;public class UtilsSelectBeanHandler { public static void main(String[] args) throws SQLException { //创建dbUtils里面的QueryRunner对象 QueryRunner queryRunner = new QueryRunner(); //sql语句 String sql = "select * from stu where age>?"; //获取数据库连接 Connection connection = JdbcUtil2.getConnection(); //存参数值的数组 Object[] params = {16}; //执行查询,并以数组的形式返回查询结果(new BeanHandler()只会返回第一条记录,并转成对象) Student student = queryRunner.query(connection,sql, new BeanHandler(Student.class),params); System.out.println(student); System.out.println("\n"); //关闭数据库连接 connection.close(); }}
5.8、创建UtilsSelectBeanListHandler类,查询数据,并将查询数据转成实际对象(BeanListHandler会返回查询到的所有记录)
1 import org.apache.commons.dbutils.QueryRunner; 2 import org.apache.commons.dbutils.handlers.BeanListHandler; 3 import util.JdbcUtil2; 4 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 import java.util.List; 8 9 public class UtilsSelectBeanListHandler {10 public static void main(String[] args) throws SQLException {11 //创建dbUtils里面的QueryRunner对象12 QueryRunner queryRunner = new QueryRunner();13 //sql语句14 String sql = "select * from stu where age>?";15 //获取数据库连接16 Connection connection = JdbcUtil2.getConnection();17 //存参数值的数组18 Object[] params = {16};19 //执行查询,并以数组的形式返回查询结果(new BeanListHandler()返回查询到的所有记录,并转成对象)20 Liststudents = queryRunner.query(connection,sql, new BeanListHandler (Student.class),params);21 System.out.println(students);22 //关闭数据库连接23 connection.close();24 }25 }
5.9、创建UtilsSelectColumnListHandler类,查询数据(ColumnListHandler会返回结果中指定的列)
1 import org.apache.commons.dbutils.QueryRunner; 2 import org.apache.commons.dbutils.handlers.ColumnListHandler; 3 import util.JdbcUtil2; 4 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 import java.util.List; 8 9 public class UtilsSelectColumnListHandler {10 public static void main(String[] args) throws SQLException {11 //创建dbUtils里面的QueryRunner对象12 QueryRunner queryRunner = new QueryRunner();13 //sql语句14 String sql = "select * from stu where age>?";15 //获取数据库连接16 Connection connection = JdbcUtil2.getConnection();17 //存参数值的数组18 Object[] params = {16};19 //执行查询,并以数组的形式返回查询结果(new ColumnListHandler<>()返回结果中指定的列)20 List
5.10、创建UtilsSelectScalarHandler类,查询单数据(ScalarHandler返回分组函数的值)
1 import org.apache.commons.dbutils.QueryRunner; 2 import org.apache.commons.dbutils.handlers.ScalarHandler; 3 import util.JdbcUtil2; 4 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 8 public class UtilsSelectScalarHandler { 9 public static void main(String[] args) throws SQLException {10 //创建dbUtils里面的QueryRunner对象11 QueryRunner queryRunner = new QueryRunner();12 //sql语句13 String sql = "select max(age) from stu";14 //获取数据库连接15 Connection connection = JdbcUtil2.getConnection();16 //存参数值的数组17 Object[] params = {};18 //执行查询,并以数组的形式返回查询结果(new ScalarHandler<>()返回分组函数的值)19 int age = queryRunner.query(connection,sql, new ScalarHandler<>(),params);20 System.out.println(age);21 //关闭数据库连接22 connection.close();23 }24 }
6、用c3p0连接池,连接数据库
6.1、在scr——pom.xml文件里,引入c3p0包
12 c3p0 3c3p0 40.9.1.2 5
6.2、在src——main——resources下增加c3p0-config.mxl文件
1 2 34 5 6 34com.mysql.jdbc.Driver 7 8jdbc:mysql://rm-uf9776rxego.mysql.rds.aliyuncs.com:3306/study 9 10root 11 12whmilyY123! 13 14 15 1630000 17 1830 19 2010 21 2230 23 24100 25 2610 27 28200 29 30200 31 323 33
6.3、在src——main——java——util里添加DataSourceUtils类
1 package util; 2 3 import com.mchange.v2.c3p0.ComboPooledDataSource; 4 5 import javax.sql.DataSource; 6 import java.sql.Connection; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement;10 11 public class DataSourceUtils {12 private static ComboPooledDataSource ds = new ComboPooledDataSource();13 14 /**15 * 获取数据源16 *17 * @return 连接池18 */19 public static DataSource getDataSource() {20 return ds;21 }22 23 /**24 * 释放资源25 *26 * @param conn27 * @param st28 * @param rs29 */30 public static void CloseResource(Connection conn, Statement st, ResultSet rs) {31 closeResultSet(rs);32 closeStaement(st);33 closeConn(conn);34 }35 36 /**37 * 获取连接38 *39 * @return 连接40 * @throws SQLException41 */42 public static Connection getConnection() throws SQLException {43 return ds.getConnection();44 }45 46 /**47 * 释放连接48 *49 * @param conn 连接50 */51 public static void closeConn(Connection conn) {52 if (conn != null) {53 try {54 conn.close();55 } catch (SQLException e) {56 e.printStackTrace();57 } finally {58 conn = null;59 }60 }61 }62 63 /**64 * 释放语句执行者65 *66 * @param st 语句执行者67 */68 public static void closeStaement(Statement st) {69 if (st != null) {70 try {71 st.close();72 } catch (SQLException e) {73 e.printStackTrace();74 } finally {75 st = null;76 }77 }78 }79 80 /**81 * 释放结果集82 *83 * @param rs 结果集84 */85 public static void closeResultSet(ResultSet rs) {86 if (rs != null) {87 try {88 rs.close();89 } catch (SQLException e) {90 e.printStackTrace();91 } finally {92 rs = null;93 }94 }95 }96 }
6.4、新建C3p0Select类,用数据库连接池的方式查询
1 import org.apache.commons.dbutils.QueryRunner; 2 import org.apache.commons.dbutils.handlers.BeanListHandler; 3 import util.DataSourceUtils; 4 5 import java.sql.SQLException; 6 import java.util.List; 7 8 public class C3p0Select { 9 public static void main(String[] args) throws SQLException {10 //创建dbUtils里面的QueryRunner对象,并获取数据库连接11 QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());12 //sql语句13 String sql = "select * from stu where age>?";14 //存参数值的数组15 Object[] params = {16};16 //执行查询,并以数组的形式返回查询结果(new BeanListHandler()返回查询到的所有记录,并转成对象)17 Liststudents = queryRunner.query(sql, new BeanListHandler (Student.class),params);18 System.out.println(students);19 }20 }
转载地址:https://superman.blog.csdn.net/article/details/109264186 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!