正文
java与sql连接代码 java连接sqlserver代码
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
java如何实现sql连接和查询的代码?
import java.sql.Connection。
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBCon {
//数据库驱动对象
public static final String DRIVER="oracle.jdbc.driver.OracleDriver";
//数据库连接地址(数据库名)
public static final String URL="jdbc:oracle:thin:@localhost:1521:orcl";
//登陆名
public static final String USER="FM";
//登陆密码
public static final String PWD="FM";
//创建数据库连接对象
private Connection con=null;
//创建数据库预编译对象
private PreparedStatement ps=null;
//创建结果集
private ResultSet rs=null;
//创建数据源对象
public static DataSource source=null;
// //静态代码块
// static{
//
// //初始化配置文件context
// try {
// Context context=new InitialContext();
// source=(DataSource)context.lookup("java:comp/env/jdbc/webmessage");
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
//
//
// }
/**
* 获取数据库连接
*/
public Connection getCon(){
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
con=DriverManager.getConnection(URL,USER,PWD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
// /**
// * 获取数据库连接
// */
// public Connection getCon(){
//
// try {
// con=source.getConnection();
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
//
// return con;
// }
/**
* 关闭所有资源
*/
public void closeAll(){
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(ps!=null)
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(con!=null)
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @param sql数据库更新(增、删、改) 语句
* @param pras参数列表(可传java与sql连接代码,可不传java与sql连接代码,不传为NULL,以数组形式存在)
* @return 返回受影响都行数
*/
public int update(String sql,String... pras){
int resu=0;
con=getCon();
try {
ps=con.prepareStatement(sql);
for(int i=0;ipras.length;i++){
ps.setString(i+1,pras[i]);
}
resu=ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
closeAll();
}
return resu;
}
/**
* @param sql数据库查询语句
* @param pras参数列表(可传,可不传,不传为NULL,以数组形式存在)
* @return 返回结果集
*/
public ResultSet query(String sql,String... pras){
con=getCon();
try {
ps=con.prepareStatement(sql);
if(pras!=null)
for(int i=0;ipras.length;i++){
ps.setString(i+1, pras[i]);
}
rs=ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
怎么用java连接SQL数据库?
如何用java连接sql的数据库 1、java数据库操作基本流程 : java数据库操作基本流程:取得数据库连接 - 执行sql语句 - 处理执行结果 - 释放数据库连接
2、几个常用的重要技巧:可滚动、更新的记录集 、 批量更新 、事务处理1、取得数据库连接1)用DriverManager取数据库连接例子:String className,url,uid,pwd;
className = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@127.0.0.1:1521:orasvr;
uid = "system";
pwd = "manager";
Class.forName(className);
Connection cn = DriverManager.getConnection(url,uid,pwd);2)用jndi(java的命名和目录服务)方式例子String jndi = "jdbc/db";
Context ctx = (Context) new InitialContext().lookup("java:comp/env");
DataSource ds = (DataSource) ctx.lookup(jndi);
Connection cn = ds.getConnection(); //多用于jsp中2、执行sql语句1)用Statement来执行sql语句String sql;
Statement sm = cn.createStatement();
sm.executeQuery(sql); // 执行数据查询语句(select)
sm.executeUpdate(sql); // 执行数据更新语句(delete、update、insert、drop等)statement.close();2)用PreparedStatement来执行sql语句String sql;
sql = "insert into user (id,name) values (?,?)";
PreparedStatement ps = cn.prepareStatement(sql);
ps.setInt(1,xxx);
ps.setString(2,xxx);
...
ResultSet rs = ps.executeQuery(); // 查询
int c = ps.executeUpdate(); // 更新3、处理执行结果查询语句,返回记录集ResultSet。更新语句,返回数字,表示该更新影响的记录数。ResultSet的方法:1、next(),将游标往后移动一行,如果成功返回true;否则返回false。2、getInt("id")或getSting("name"),返回当前游标下某个字段的值。3、释放连接。cn.close();一般,先关闭ResultSet,然后关闭Statement(或者PreparedStatement);最后关闭Connection可滚动、更新的记录集1、创建可滚动、更新的StatementStatement sm=cn.createStatement(ResultSet.TYPE_SCROLL_ENSITIVE,
ResultSet.CONCUR_READ_ONLY);该Statement取得的ResultSet就是可滚动的2、创建PreparedStatement时指定参数PreparedStatemet ps=cn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);ResultSet.absolute(9000);批量更新1、StatementStatement sm = cn.createStatement();
sm.addBatch(sql1);
sm.addBatch(sql2);
...
sm.executeBatch()一个Statement对象,可以执行多个sql语句以后,批量更新。这多个语句可以是delete、update、insert等或兼有2、PreparedStatementPreparedStatement ps = cn.preparedStatement(sql);
{
ps.setXXX(1,xxx);
...
ps.addBatch();
}
ps.executeBatch();一个PreparedStatement,可以把一个sql语句,变换参数多次执行,一次更新。事务的处理1、关闭Connection的自动提交cn.setAutoCommit(false);2、执行一系列sql语句要点:执行每一个新的sql语句前,上一次执行sql语句的Statement(或者PreparedStatemet)必须先closeStatement sm ;
sm = cn.createStatement(insert into user...);
sm.executeUpdate();
sm.close();sm = cn.createStatement("insert into corp...);
sm.executeUpdate();
sm.close();3、提交cn.commit();
Java连接SQL2005数据库
你需要放一个sql2005的驱动放到项目中的bin文件夹中新建的一个lib文件夹里面,然后那个conn连接的代码可以这样写:conn
=
DriverManager.getConnection("jdbc:odbc:Driver={SQL
SERVER};server=.;database=数据库名;");这样写是通用的写法。
java连接数据库的代码
用这个类吧.好的话,给我加加分.
import java.sql.*;
/**
* @功能: 一个JDBC的本地化API连接类,封装了数据操作方法,只用传一个SQL语句即可
* @作者: 李开欢
* @日期: 2007/
*/
public class ConnectionDemo {
/*
* 这里可以将常量全部放入另一个类中,以方便修改
*/
private static Connection conn;
private static Statement ps;
private static ResultSet rs;
private static final String DRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
private static final String URL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb";
private static final String USER ="sa";
private static final String PASS = "sa";
public ConnectionDemo() {
// TODO Auto-generated constructor stub
ConnectionDemo.getConnection();
}
public static Connection getConnection(){
System.out.println("连接中...");
try {
Class.forName(ConnectionDemo.DRIVER);
conn = DriverManager.getConnection(ConnectionDemo.URL, ConnectionDemo.USER, ConnectionDemo.PASS);
System.out.println("成功连接");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static Statement getStatement(String sql){
System.out.println("执行SQL语句中...");
try {
ps = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
if(sql.substring(0, 6).equals("select")){
rs = ps.executeQuery(sql);
System.out.println("执行完查询操作,结果已返回ResultSet集合");
}else if(sql.substring(0, 6).equals("delete")){
ps.executeUpdate(sql);
System.out.println("已执行完毕删除操作");
}else if(sql.substring(0, 6).equals("insert")){
ps.executeUpdate(sql);
System.out.println("已执行完毕增加操作");
}else{
ps.executeUpdate(sql);
System.out.println("已执行完毕更新操作");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ps;
}
public static ResultSet getResultSet(){
System.out.println("查询结果为:");
return rs;
}
public static void closeConnection(){
System.out.println("关闭连接中...");
try {
if (rs != null) {
rs.close();
System.out.println("已关闭ResultSet");
}
if (ps != null) {
ps.close();
System.out.println("已关闭Statement");
}
if (conn != null) {
conn.close();
System.out.println("已关闭Connection");
}
} catch (Exception e) {
// TODO: handle exception
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
ConnectionDemo.getConnection();
String sql = "delete from type where id = 1";
ConnectionDemo.getStatement(sql);
String sql2 = "insert into type values(1,'教学设备')";
ConnectionDemo.getStatement(sql2);
String sql1 = "select * from type";
ConnectionDemo.getStatement(sql1);
ResultSet rs = ConnectionDemo.getResultSet();
System.out.println("编号 "+"类 型");
try {
while(rs.next()){
System.out.print(" "+rs.getInt(1)+" ");
System.out.println(rs.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ConnectionDemo.closeConnection();
}
}
求JAVA连接SQL数据库的代码
package com.conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
public class ConPool {
private String strDriverClass;
private String strJdbcUrl;
private String strUserName;
private String strUserPwd;
public ConPool(){
}
public Connection getMyJdbcConn()
{
Connection myConn=null;
try {
Class.forName(this.getStrDriverClass()).newInstance();
//DriverManager.registerDriver (new org.postgresql.Driver());
myConn= DriverManager.getConnection(this.getStrJdbcUrl(),this.getStrUserName(),this.getStrUserPwd());
}catch (Exception ex) {
System.out.print(ex);
}
return myConn;
}
public void closeConn(Connection myConn)
{
if (myConn!=null) {
try{
myConn.close();
myConn=null;
}catch(Exception e){}
}
}
/**
* @return the strDriverClass
*/
public String getStrDriverClass() {
return strDriverClass;
}
/**
* @param strDriverClass the strDriverClass to set
*/
public void setStrDriverClass(String strDriverClass) {
this.strDriverClass = strDriverClass;
}
/**
* @return the strJdbcUrl
*/
public String getStrJdbcUrl() {
return strJdbcUrl;
}
/**
* @param strJdbcUrl the strJdbcUrl to set
*/
public void setStrJdbcUrl(String strJdbcUrl) {
this.strJdbcUrl = strJdbcUrl;
}
/**
* @return the strUserName
*/
public String getStrUserName() {
return strUserName;
}
/**
* @param strUserName the strUserName to set
*/
public void setStrUserName(String strUserName) {
this.strUserName = strUserName;
}
/**
* @return the strUserPwd
*/
public String getStrUserPwd() {
return strUserPwd;
}
/**
* @param strUserPwd the strUserPwd to set
*/
public void setStrUserPwd(String strUserPwd) {
this.strUserPwd = strUserPwd;
}
public static void main(String[] args) {
Connection crmCon=null;
ConPool cp = new ConPool();
// //MySQL数据库
// cp.setStrDriverClass("com.mysql.jdbc.Driver");
// cp.setStrJdbcUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=truecharacterEncoding=gbk");
// cp.setStrUserName("test");
// cp.setStrUserPwd("test");
//postgre数据库
cp.setStrDriverClass("org.postgresql.Driver");
cp.setStrJdbcUrl("jdbc:postgresql://127.0.0.1:5432/test");
cp.setStrUserName("test");
cp.setStrUserPwd("test");
crmCon=cp.getMyJdbcConn();
if (crmCon!=null) {
System.out.println("通过jdbc连接成功!");
}
// try {
// Statement stmtCrmUpd = crmCon.createStatement();
//
// stmtCrmUpd.addBatch("truncate table trade.sms_tmp_feild ");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (0, 'membername', '%s', '会员姓名')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (1, 'cardwh', '%s', '卡号末四位')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (2, 'transmerchant', '%s', '交易店')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (3, 'transtype', '%s', '交易类型')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (4, 'transamount', '%.2f', '交易金额')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (5, 'cardbalance', '%.2f', '卡余额')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (6, 'merchantbrand', '%s', '交易商户集团名称')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (7, 'couponname', '%s', '券名称')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (9, 'cardno', '%s', '卡号')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (10, 'cardmerchant', '%s', '卡属商户名')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (11, 'transdate', '%tm月%te日', '交易日期')");
// stmtCrmUpd.addBatch("INSERT INTO trade.sms_tmp_feild VALUES (12, 'transtime', '%tT', '交易时间')");
//
// crmCon.setAutoCommit(false);
// //成功的话再提交保存当前最大ID号和失败日志
// stmtCrmUpd.executeBatch();
// crmCon.commit();
// crmCon.setAutoCommit(true);
//
// }catch (Exception ex) {
// System.out.println("数据库执行异常!");
// System.out.println(ex);
// try {
// crmCon.rollback();
// }catch (Exception e) {
// System.out.println("数据库回滚异常!");
// System.out.println(e);
// }
// }
//
// ResultSet rset = null;
//
// String sql = "select field_name,field_type from trade.sms_tmp_feild";
// MapString,String m = new HashMapString,String();
// try {
// Statement stmtCrm = crmCon.createStatement();
// rset = stmtCrm.executeQuery(sql);
//
// if (rset!=null){
// while (rset.next()){
// m.put(rset.getString("field_name"), rset.getString("field_type"));
// }
// rset.close();
// }
// rset = null;
// stmtCrm.close();
// stmtCrm = null;
// }catch (Exception ex) {
// System.out.println("数据库执行异常!");
// System.out.println(ex);
// }
//
//
// String strSql = "SELECT id,replace(replace(replace(replace(replace(replace(message_template,'tm月%te日',''),'d',''),'.2f',''),'tF',''),'tT',''),'s','') as message_template," +
// "template_feilds FROM trade.message_template";
//
// try {
// Statement stmtCrm = crmCon.createStatement();
// Statement stmtCrmUpd = crmCon.createStatement();
// rset = stmtCrm.executeQuery(strSql);
//
// if (rset!=null){
// while (rset.next()){
// if (rset.getString("template_feilds")!=nullrset.getString("message_template")!=null){
// String[] aryFeild = rset.getString("template_feilds").split(",");
// String[] aryCon = rset.getString("message_template").split("%");
// String tid = rset.getString("id");
//
// if (aryFeild!=nullaryFeild.length0aryCon!=nullaryCon.length0){
// StringBuilder strB = new StringBuilder();
// StringBuilder strF = new StringBuilder();
// StringBuilder bSql = new StringBuilder();
// strB.append(aryCon[0]);
// for (int i=1;iaryCon.length;i++){
// String strFormatCon = m.get(aryFeild[i-1]);
// if (strFormatCon.indexOf("%", 1)0){
// strF.append(aryFeild[i-1]+",");
// }
// strF.append(aryFeild[i-1]+",");
// strB.append(strFormatCon);
// strB.append(aryCon[i]);
// }
//
// if (strF.lastIndexOf(",")==strF.length()-1){
// strF.delete(strF.length()-1, strF.length());
// }
// System.out.println("原模板内容:"+rset.getString("message_template"));
// System.out.println("新摸版内容:"+strB.toString());
// System.out.println("原模板字段:"+rset.getString("template_feilds"));
// System.out.println("新模板字段:"+strF.toString());
//
// bSql.append("update trade.message_template set message_template='"+strB.toString()+"',template_feilds='"+strF.toString()+"' where id="+tid);
//
// System.out.println("修改模板Sql:"+strF.toString());
//
// stmtCrmUpd.addBatch(bSql.toString());
// }
// }
// }
// rset.close();
// }
// rset = null;
// stmtCrm.close();
// stmtCrm = null;
//
// crmCon.setAutoCommit(false);
// stmtCrmUpd.executeBatch();
// crmCon.commit();
// crmCon.setAutoCommit(true);
//
// stmtCrmUpd.close();
// stmtCrmUpd = null;
// }catch (Exception ex) {
// System.out.println("数据库执行异常!");
// System.out.println(ex);
// try {
// crmCon.rollback();
// }catch (Exception e) {
// System.out.println("数据库回滚异常!");
// System.out.println(e);
// }
// }
cp.closeConn(crmCon);
}
}
关于java与sql连接代码和java连接sqlserver代码的介绍到此就结束了,不知道你从中找到你需要的信息了吗 ?如果你还想了解更多这方面的信息,记得收藏关注本站。