本笔记只是自己学习之后巩固用,所以有些东西懒省事,根本不合理,看破不说破。。。
最常用的连接方式:
1.创建DBUtil类,存储连接数据库公共语句;
public class DButils {
private static String url;
private static String user;
private static String password;
private static String driverClass;
static{
//本来该是在propertise文件里存储,但是我用的eclipse没有这个后缀文件;
driverClass="com.mysql.jdbc.Driver";//基本固定,必须有mysql的jar包
url="jdbc:mysql://127.0.0.1:3306/jdbc";//127.0.0.1为本机ip,本机可以用localhost;3306为端口
user="root";//用户名
password="abc123";//密码
try {
Class.forName(driverClass);//加载驱动
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
//连接数据库
return (Connection) DriverManager.getConnection(url,user,password);
}
public static void closeAll(Connection conn,Statement stmt,ResultSet rs){
//关闭数据库
if(rs!=null)
{
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs=null;
}
if(stmt!=null)
{
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt=null;
}
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn=null;
}
}
}
2.创建数据库语句工具类
//查询语句
public static void Select(){
//初始化
Connection conn=null;
PreparedStatement stmt=null;//注意使用PreparedStatement,可以固定sql语句格式,防止sql注入
ResultSet rs=null;
try {
conn=DButils.getConnection();//连接数据库
stmt=(PreparedStatement) conn.prepareStatement("select * from user");//固化sql语句
rs=(ResultSet) stmt.executeQuery();//得到查询结果
java.util.List<User> list=new ArrayList<User>();//使用泛型集合存储结果,User是一个数据库存储对象类
while(rs.next()){
//遍历读取并存储
User u=new User();
u.setName(rs.getString("name"));
u.setAge(rs.getInt("age"));
list.add(u);
}
for (User user : list) {
System.out.print(user.getAge());
System.out.println(user.getName());
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DButils.closeAll(conn, stmt, rs);
//关闭数据库连接
}
}
//插入语句
public static void Insert(){
Connection conn=null;
Statement stmt=null;
try {
conn=DButils.getConnection();
stmt=(Statement) conn.createStatement();
int i=stmt.executeUpdate("insert into user values('张三',20)");
if(i>0){
System.out.println("成功");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DButils.closeAll(conn, stmt, null);
}
}
//删除语句
public static void Delete(){
Connection conn=null;
Statement stmt=null;
try {
conn=DButils.getConnection();
stmt=(Statement) conn.createStatement();
int i=stmt.executeUpdate("delete from user where age=20");
if(i>0){
System.out.println("成功");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DButils.closeAll(conn, stmt, null);
}
}
//更改语句
public static void Updata(){
Connection conn=null;
Statement stmt=null;
try {
conn=DButils.getConnection();
stmt=(Statement) conn.createStatement();
int i=stmt.executeUpdate("update user set name='aaa' where age=20");
if(i>0){
System.out.println("成功");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DButils.closeAll(conn, stmt, null);
}
}