JDBC写成工具类
工具类
public class jdbcUtils {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/product";
private static String name = "root";
private static String mima = "root";
static{
// 因为只要注册一次,写成静态代码块,注册驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
//注册驱动如果失败,就直接停了
throw new RuntimeException("注册驱动失败");
}
}
public static Connection getConnection() throws Exception {
// 连接数据库
Connection con = DriverManager.getConnection(url, name, mima);
//返回值
return con;
}
public static void closeAll(Connection con,Statement st,ResultSet rs){
if(con !=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
调用
public class Test {
public static void main(String[] args) {
//注册驱动+连接数据库
Connection con = null;
//执行语句
Statement st = null;
//操作结果集
ResultSet rs = null;
try {
con = jdbcUtils.getConnection();
st = con.createStatement();
rs = st.executeQuery("select *from people");
//读取结果集
while(rs.next()){
Object pname=rs.getObject("pname");
Object job =rs.getObject("job");
System.out.println(pname+" | "+job);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭资源
jdbcUtils.closeAll(con, st, rs);
}
}
}
增删改查练习
要用prepareStatement
例子:
String sql=“select * from 表名 where cid=? and name=?”;
//先预处理
prepareStatement ps=con.prepareStatement(sql)
//把上面?的地方,替换掉(第几个"?"序号从1开始,替换的值)
ps.setObject(1, 替换的值);
ps.setObject(2, 替换的值);
//创建结果集
rs = ps.executeQuery();
public class Test02 {
public static void main(String[] args) {
//zeng();
//shan();
//gai();
cha();
}
public static void zeng(){
Connection con=null;
Statement st=null;
try{
//注册驱动+连接数据库
con =jdbcUtils.getConnection();
//创建执行语句
st =con.createStatement();
//操作结果集
int result=st.executeUpdate("insert into people(pname) values('java练习2')");
System.out.println(result);
}catch(Exception e){
e.printStackTrace();
}finally{
System.out.println("关闭资源");
jdbcUtils.closeAll(con, st,null);
}
}
public static void shan(){
Connection con=null;
Statement st=null;
try{
//注册驱动
con= jdbcUtils.getConnection();
//执行语句
st=con.createStatement();
//操作
int result =st.executeUpdate("delete from people where empno=9999");
System.out.println(result);
}catch(Exception e){
e.printStackTrace();
}finally{
jdbcUtils.closeAll(con, st, null);
}
}
public static void gai(){
Connection con=null;
Statement st=null;
try{
//注册驱动+连接数据库
con =jdbcUtils.getConnection();
//创建执行语句
st= con.createStatement();
//操作
int result =st.executeUpdate("update people set pname=('张三') where empno=7935");
System.out.println(result);
}catch(Exception e){
e.printStackTrace();
}finally{
jdbcUtils.closeAll(con, st,null);
}
}
public static void cha(){
Connection con=null;
Statement st=null;
ResultSet rs=null;
try{
//注册驱动+连接数据库
con=jdbcUtils.getConnection();
//创建执行语句
st= con.createStatement();
//创建结果集
rs=st.executeQuery("select *from people");
//读取结果集
System.out.println("工号"+" | "+"姓名"+" | "+"工作"+" | "+"mgr"+" | "+"sal"+" | "+"comm");
while(rs.next()){
Object empno= rs.getObject("empno");
Object pname= rs.getObject("pname");
Object job= rs.getObject("job");
Object mgr= rs.getObject("mgr");
Object sal= rs.getObject("sal");
Object comm= rs.getObject("comm");
System.out.println(empno+" | "+pname+" | "+job+" | "+mgr+" | "+sal+" | "+comm);
}
}catch(Exception e){
e.printStackTrace();
}finally{
jdbcUtils.closeAll(con, st, rs);
}
}
}
登录账号小练习
这里弃用createStatement,因为会sql注入
//输入账号密码
Scanner sc = new Scanner(System.in);
System.out.println("账号");
String zhanghao=sc.nextLine();
System.out.println("密码");
String mima=sc.nextLine();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//注册驱动+连接数据库
con = tools.lianjie();
//创建执行语句
//prepareStatement语法,不要直接替换,写成?
String sql="select *from people where user=? and password=?";
ps = con.prepareStatement(sql);
//把上面?的地方,替换掉(第几个"?"序号从1开始,替换的值)
ps.setObject(1, zhanghao);
ps.setObject(2, mima);
//创建结果集
rs = ps.executeQuery();
//根据结果集给出结果
if(rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//释放资源
tools.closeAll(con, ps, rs);
用Properties配置数据库文件
好处:不需要在工具类中改动数据库信息
1.写一个配置文件
1.新建一个文件config.properties
2.写入数据库信息
url=jdbc:mysql://localhost:3306/product
driver=com.mysql.jdbc.Driver
user=root
password=root
2.修改工具类中的代码
public class tools {
//改动1
//用配置文件的方法,这里只要创建对象就好
static String url=null;
static String driver=null;
static String user=null;
static String password=null;
//注册驱动
static {
try {
改动2
//创建配置文件对象
Properties pt=new Properties();
//读取配置文件中的内容
pt.load(new FileInputStream("config.properties"));
//取出对应的值
driver= pt.getProperty("driver");
url=pt.getProperty("url");
user=pt.getProperty("user");
password=pt.getProperty("password");
//正式注册
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException("驱动加载异常,请重试");
}
}
public static Connection lianjie() throws Exception{
//创建连接
Connection con=DriverManager.getConnection(url, user, password);
return con;
}
public static void closeAll(Connection con,Statement st,ResultSet rs){
//关闭资源
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st !=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(rs !=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}