用jdbc连接mysql数据库时为了防止代码冗余,于是可以编写工具类
首先,线写一个配置文件
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
username就用自己mysql的用户名,password也是
然后再去编写一个工具类
package com.gc.utils; import java.sql.*; import java.util.Properties; import java.io.InputStream; import java.io.IOException; public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1.驱动只用加载一次 Class.forName(driver); } catch(IOException | ClassNotFoundException e) { e.printStackTrace(); } } /** * 获取连接资源 * */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } /** * 释放连接资源 * 分别是Connection,Statement,ResultSet * */ public static void release(Connection con, Statement st, ResultSet rs){ if (rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(con!=null){ try { con.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
之后编写测试文件测试
增
package com.gc; import com.gc.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Testinsert { public static void main(String[] args) { Connection conn = null; Statement st =null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL执行对象 String sql ="INSERT INTO USER(`id`,`name`,`password`,`email`,`birthday`)" + "VALUE('1','miaomiao','123456','123456@qq.com','2001-01-01')"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils.release(conn,st,rs); } } }
删
package com.gc; import com.gc.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestDelete { public static void main(String[] args) { Connection conn = null; Statement st =null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL执行对象 String sql ="DELETE FROM user WHERE id=1"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("删除成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils.release(conn,st,rs); } } }
改
package com.gc; import com.g.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestUpdate { public static void main(String[] args) { Connection conn = null; Statement st =null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL执行对象 String sql ="\n" + "UPDATE `user` SET `name`='xt' ,`email`='123456@qq.com' WHERE id=1"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("更新成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils.release(conn,st,rs); } } }
查
package com.gc; import com.gc.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestSelect { public static void main(String[] args) { Connection conn = null; Statement st =null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获取SQL执行对象 String sql ="select * from user where id =1"; rs = st.executeQuery(sql); while(rs.next()){ String name=rs.getString("name"); String email=rs.getString("email"); System.out.println(name); System.out.println(email); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils.release(conn,st,rs); } } }
编写sql语句可以先去sqloyg编写语句测试
再复制到idea