连接数据库JDBC

数据库的连接代码

package com.lovo.test;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;






public class TestMain {


public static void main(String[] args) {
// TODO 自动生成的方法存根
//1.加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");//反射代码---类加载
} catch (ClassNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}

//2.获取连接
Connection con = null;
//有些机器不认localhost本地连接的话，加IP地址127.0.0.1,不加“?useSSL=true”显示红字，JDK1.5以后要求数据传输使用SSL加密
String url = "jdbc:mysql://localhost:3306/129?useSSL=true&useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123";
try {
con = DriverManager.getConnection(url, username, password);
//System.out.println(con);//查看是否成功连接，成功则打印出对象的十六进制数

//3.构建语句对象
// String sql = "insert into t_bus(f_bus) values ('按位')";
// String sql = "update t_womans set f_weight = 48 where fk_link = 1";
// String sql = "delete from t_womans where f_id = 4";
String sql = "delete from t_bus where pk_id > 4";
Statement state = con.createStatement();//通过连接获取语句对象

//4.语句对象执行SQL
int r =state.executeUpdate(sql);//所有的DML语句都调用这个方法，该方法默认返回影响了多少行

//新增数据时，有时候会需要得到自动生成的主键值
// int r = state.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

System.out.println(r);

} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} finally{
//5.用完以后要关掉谨记！！！
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}


}


}

package com.lovo.test;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import com.lovo.bean.GroupBean;
import com.lovo.bean.TeamBean;


public class TestDQL {


/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub


try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

Connection con = null;
ArrayList<GroupBean> allGroup = new ArrayList<GroupBean>();
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test129?useSSL=true&useUnicode=true&characterEncoding=utf8", "root", "root");

//单表操作!
String sql = "select * from t_group";
Statement state = con.createStatement();

ResultSet rs = state.executeQuery(sql);//DQL语句通用这个方法
while(rs.next()){
GroupBean gb = new GroupBean();
gb.setId( rs.getInt("pk_groupid"));
gb.setGroupName(rs.getString("f_groupName"));
gb.setGroupAddress(rs.getString("f_groupAddress"));
gb.setGroupNum(rs.getInt("f_groupNum"));

allGroup.add(gb);
}


for(GroupBean gb : allGroup){
System.out.println(gb.getGroupName());
}

//联表查询
// GroupBean group = new GroupBean();
// String sql = "select * from t_team join t_group on t_team.fk_groupid=t_group.pk_groupid where t_group.f_groupName='A组'";
// Statement state = con.createStatement();
// ResultSet rs = state.executeQuery(sql);
// while(rs.next()){
// group.setId(rs.getInt("pk_groupid"));
// group.setGroupName(rs.getString("f_groupName"));
// group.setGroupAddress(rs.getString("f_groupAddress"));
// group.setGroupNum(rs.getInt("f_groupNum"));
//
// TeamBean tb = new TeamBean();
// tb.setId(rs.getInt("pk_teamid"));
// tb.setTeamName(rs.getString("f_teamname"));
// tb.setTeamCoach(rs.getString("f_teamcoach"));
// tb.setTeamCountry(rs.getString("f_teamcountry"));
// tb.setTeamNum(rs.getInt("f_teamnum"));
//
// group.getTeamLst().add(tb);
// }
//
//
// System.out.println(group.getGroupName());
// System.out.println(group.getTeamLst().size());
// for(TeamBean tb : group.getTeamLst()){
// System.out.println(tb.getTeamName());
// }





} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

}


}

事务

package com.lovo.test;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


//事务操作
public class TestTransaction {


/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");//反射代码---类加载
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

//2、获取连接
Connection con = null;
String url = "jdbc:mysql://localhost:3306/test129?useSSL=true&useUnicode=true&characterEncoding=utf8";//协议://ip地址:端口号/数据库名
String username = "root";
String password = "root";
try {
con = DriverManager.getConnection(url,username,password);

String insertSQL = "insert into t_team(f_teamName,f_teamCountry,f_teamCoach,f_teamNum,fk_groupid) values(?,?,?,?,?)";
String updateSQL = "update t_group set t_groupNum = f_groupNum + 1 where pk_groupid = ?";

//1、将连接对象的自动提交设置为假
con.setAutoCommit(false);
PreparedStatement insertPS = con.prepareStatement(insertSQL);
insertPS.setString(1, "**队");
insertPS.setString(2, "****国");
insertPS.setString(3, "**教练");
insertPS.setInt(4, 23);
insertPS.setInt(5, 7);

PreparedStatement updatePS = con.prepareStatement(updateSQL);
updatePS.setInt(1, 7);

insertPS.executeUpdate();
updatePS.executeUpdate();

con.commit();//2、手动提交

}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
try {
con.rollback();//3、回滚
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} finally{
//5、关闭连接
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}


}