mysql的jdbc案例_MYSQL连接jdbc增删改查案例

下面的这个代码是为了避免重复的进行相同的操作

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

public class JDBCUtils {

private static Properties pro =null;

static {

InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream(“db.properties”);

//定义properties对象，把读取出来的内容放在properties里面

pro =new Properties();

try {

pro.load(in);

Class.forName(pro.getProperty("driverName"));

}catch(Exception e) {

e.printStackTrace();

}

}

public static Connection getConnection() throws SQLException {

return DriverManager.getConnection(pro.getProperty("url"), pro.getProperty("user"), pro.getProperty("password"));

}

}

/**

进行数据库的增删改查操作

*/

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.junit.Test;

public class tb_student_info_test {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

/**

查询

@throws SQLException

*/

@Test

public void query() throws SQLException {

try {

conn = JDBCUtils.getConnection();

String sql = “select * from tb_film_info where f_name=?”;

ps = conn.prepareStatement(sql); ps.setString(1, "神话");

System.out.println(sql);

rs = ps.executeQuery();

int columnCount = rs.getMetaData().getColumnCount();

System.out.println("总共有:" + columnCount);

while (rs.next()) {

for (int i = 1; i <= columnCount; i++) {

System.out.print(rs.getString(i));

if (i < columnCount) {

System.out.print(",");

}

}

System.out.println();

}

} catch (Exception e) {

e.printStackTrace();

} finally {

if (rs != null) {

rs.close();

}

if (ps != null) {

ps.close();

}

if (conn != null) {

conn.close();

}

}

}

/**

添加

@throws SQLException

*/

@Test

public void insertNews() throws SQLException {

try {

conn = JDBCUtils.getConnection();

String sql=“insert into tb_film_info(f_name,f_type,f_score,f_play_year,f_origin) values(?,?,?,?,?)”;

ps=conn.prepareStatement(sql);

ps.setString(1, “动物世界”);

ps.setString(2, “动作片”);

ps.setInt(3, 100);

ps.setInt(4, 2019);

ps.setString(5, “中国”);

int i = ps.executeUpdate();

System.out.println(“成功添加”+i+“条数据”);

}catch(Exception e) {

e.printStackTrace();

}finally {

if(rs!=null) {rs.close();}

if(ps!=null) {ps.close();}

if(conn!=null) {conn.close();}

}

}

/**

修改

@throws SQLException

*/

@Test

public void updateNews() throws SQLException {

try {

conn=JDBCUtils.getConnection();

String sql=“update tb_film_info set f_score=? where f_name=?”;

ps=conn.prepareStatement(sql);

ps.setInt(1, 98);

ps.setString(2, “动物世界”);

int i = ps.executeUpdate();

System.out.println(“成功修改”+i+“条数据”);}

catch(Exception e) {

e.printStackTrace();

}finally {

if(rs!=null) {

rs.close();

}

if(ps!=null) {

ps.close();

}

if(conn!=null) {

conn.close();

}

}

}

/**

删除

*/

@Test

public void deleteNews() throws SQLException {

try {

conn=JDBCUtils.getConnection();

String sql=“delete from tb_film_info where f_name=?”;

ps=conn.prepareStatement(sql);

ps.setString(1, “动物世界”);

int i = ps.executeUpdate();

System.out.println(“成功删除”+i+“条数据”);}

catch(Exception e) {

e.printStackTrace();

}finally {

if(rs!=null) {

rs.close();

}

if(ps!=null) {

ps.close();

}

if(conn!=null) {

conn.close();

}

}

}

}

总结:

获取元数据(列)

ResultSet:getMetaData(); 得到元数据；返回的是:ResultSetMetaData对象

getColumnCount();返回有几个列

PreparedStatement

他是Statement接口的子接口

强大之处

防止sql攻击

提高代码的可维护性

提高效率

PreparedStatement 用法

1.先给出sql模板

参数:都用 ?代替

2.调用Connection 的preparedStatement(sql模板);

返回的PreparedStatement;

3.调用setXXX()

setInt(index,value);

setString(index,value);

注意:有几个? 就设置几个值