src/de/bezier/data/sql/SQL.java
public void insertUpdateInDatabase ( String tableName, String[] columnNames, Object[] values )
	{
		HashMap<Object, Object> valuesKeys = new HashMap<Object,Object>();
		for ( int i = 0; i < values.length; i++ )
		{
			valuesKeys.put(columnNames[i], values[i]);
		}

		HashMap<String, Object> primaryKeys = null;
		try {
			DatabaseMetaData meta = connection.getMetaData();
			ResultSet rs = meta.getPrimaryKeys(null, null, tableName);

		    while ( rs.next() )
			{
				if ( primaryKeys == null )
			    	primaryKeys = new HashMap<String, Object>();

				String columnName = rs.getString("COLUMN_NAME");
		    	primaryKeys.put(columnName, valuesKeys.get(columnName));
				valuesKeys.remove(columnName);
		    }

		} catch ( SQLException sqle ) {
			sqle.printStackTrace();
		}

		//System.out.println(valuesKeys);
		//System.out.println(primaryKeys);

		String cols = "";
		String patt = "";
		HashMap<Object, Integer> valueIndices = new HashMap<Object, Integer>();
		int i = 1;
		for ( Map.Entry e : valuesKeys.entrySet() )
		{
			cols += ( i > 1 ? " , " : "" ) + e.getKey();
			patt += ( i > 1 ? " , " : "" ) + "?";
			valueIndices.put( e.getKey(), i );
			i++;
		}

		String sql = null, opts = null;
		HashMap<Object, Integer> primaryIndices = null;
		if ( primaryKeys == null || primaryKeys.size() == 0 ) {
			sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )";
		} else {
			primaryIndices = new HashMap<Object, Integer>();
			opts = " WHERE ";
			int p = 1;
			for ( Map.Entry e : primaryKeys.entrySet() ) {
				opts += (p > 1 ? " , " : "") + e.getKey() + " = ? ";
				primaryIndices.put( e.getKey(), p );
				p++;
			}
			//System.out.println( opts );
			String sqlFind = "SELECT * FROM "+tableName+" "+opts;
			//System.out.println( sqlFind );
			try {
				PreparedStatement psFind = connection.prepareStatement( sqlFind );
				for ( Map.Entry e : primaryKeys.entrySet() ) {
					psFind.setString( primaryIndices.get(e.getKey()), e.getValue().toString() );
				}
				result = psFind.executeQuery();
				boolean found = next();
				psFind.close();
				if ( !found ) {
					if (DEBUG) System.out.println(String.format(
						"No entry with %s found in table '%s'", primaryKeys.toString(), tableName
					));
					int k = 1, m = valueIndices.size();
					for ( Map.Entry e : primaryKeys.entrySet() ) {
						cols += ( m > 0 ? " , " : "" ) + e.getKey();
						patt += ( m > 0 ? " , " : "" ) + "?";
						valuesKeys.put( e.getKey(), e.getValue() );
						valueIndices.put( e.getKey(), m+k );
						k++;
					}
					sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )";
					primaryKeys = null;
				}
				else
				{
					cols = "";
					for ( Map.Entry e : valuesKeys.entrySet() )
					{
						cols += ( cols.equals("") ? "" : " , " ) + e.getKey() + " = " + "?";
					}
					sql = "UPDATE "+tableName+" SET " + cols + " " + opts;
				}
			} catch ( java.sql.SQLException sqle ) {
				sqle.printStackTrace();
			}
		}

		if (DEBUG) System.out.println( sql );

		try {
			PreparedStatement ps = connection.prepareStatement( sql );

			for ( Map.Entry e : valuesKeys.entrySet() )
			{
				ps.setString( valueIndices.get(e.getKey()), e.getValue()+"" );
			}

			if ( primaryKeys != null )
			{
				for ( Map.Entry e : primaryKeys.entrySet() )
				{
					ps.setString( valueIndices.size()+primaryIndices.get(e.getKey()), e.getValue()+"" );
				}
			}

			ps.executeUpdate();
			ps.close();
		} catch ( java.sql.SQLException sqle ) {
			sqle.printStackTrace();
		}
	}
/**
	 *	Insert or update a bunch of values in the database. If the given table has a
	 *	primary key the entry will be updated if it already existed.
	 *
	 *	@param tableName String The name of the table
	 *	@param columnNames String[] The names of the columns to fill or update
	 *	@param values Object[] The values to instert or update
	 */
Insert or update a bunch of values in the database. If the given table has a
primary key the entry will be updated if it already existed.

@param tableName String The name of the table
@param columnNames String[] The names of the columns to fill or update
@param values Object[] The values to instert or update
Insert or update a bunch of values in the database . If the given table has a primary key the entry will be updated if it already existed . @param tableName String The name of the table @param columnNames String [] The names of the columns to fill or update @param values Object [] The values to instert or update
public void insertUpdateInDatabase ( String tableName, String[] columnNames, Object[] values )
	{
		HashMap<Object, Object> valuesKeys = new HashMap<Object,Object>();
		for ( int i = 0; i < values.length; i++ )
		{
			valuesKeys.put(columnNames[i], values[i]);
		}
		HashMap<String, Object> primaryKeys = null;
		try {
			DatabaseMetaData meta = connection.getMetaData();
			ResultSet rs = meta.getPrimaryKeys(null, null, tableName);
		    while ( rs.next() )
			{
				if ( primaryKeys == null )
			    	primaryKeys = new HashMap<String, Object>();
				String columnName = rs.getString("COLUMN_NAME");
		    	primaryKeys.put(columnName, valuesKeys.get(columnName));
				valuesKeys.remove(columnName);
		    }
		} catch ( SQLException sqle ) {
			sqle.printStackTrace();
		}
		String cols = "";
		String patt = "";
		HashMap<Object, Integer> valueIndices = new HashMap<Object, Integer>();
		int i = 1;
		for ( Map.Entry e : valuesKeys.entrySet() )
		{
			cols += ( i > 1 ? " , " : "" ) + e.getKey();
			patt += ( i > 1 ? " , " : "" ) + "?";
			valueIndices.put( e.getKey(), i );
			i++;
		}
		String sql = null, opts = null;
		HashMap<Object, Integer> primaryIndices = null;
		if ( primaryKeys == null || primaryKeys.size() == 0 ) {
			sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )";
		} else {
			primaryIndices = new HashMap<Object, Integer>();
			opts = " WHERE ";
			int p = 1;
			for ( Map.Entry e : primaryKeys.entrySet() ) {
				opts += (p > 1 ? " , " : "") + e.getKey() + " = ? ";
				primaryIndices.put( e.getKey(), p );
				p++;
			}
			String sqlFind = "SELECT * FROM "+tableName+" "+opts;
			try {
				PreparedStatement psFind = connection.prepareStatement( sqlFind );
				for ( Map.Entry e : primaryKeys.entrySet() ) {
					psFind.setString( primaryIndices.get(e.getKey()), e.getValue().toString() );
				}
				result = psFind.executeQuery();
				boolean found = next();
				psFind.close();
				if ( !found ) {
					if (DEBUG) System.out.println(String.format(
						"No entry with %s found in table '%s'", primaryKeys.toString(), tableName
					));
					int k = 1, m = valueIndices.size();
					for ( Map.Entry e : primaryKeys.entrySet() ) {
						cols += ( m > 0 ? " , " : "" ) + e.getKey();
						patt += ( m > 0 ? " , " : "" ) + "?";
						valuesKeys.put( e.getKey(), e.getValue() );
						valueIndices.put( e.getKey(), m+k );
						k++;
					}
					sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )";
					primaryKeys = null;
				}
				else
				{
					cols = "";
					for ( Map.Entry e : valuesKeys.entrySet() )
					{
						cols += ( cols.equals("") ? "" : " , " ) + e.getKey() + " = " + "?";
					}
					sql = "UPDATE "+tableName+" SET " + cols + " " + opts;
				}
			} catch ( java.sql.SQLException sqle ) {
				sqle.printStackTrace();
			}
		}
		if (DEBUG) System.out.println( sql );
		try {
			PreparedStatement ps = connection.prepareStatement( sql );
			for ( Map.Entry e : valuesKeys.entrySet() )
			{
				ps.setString( valueIndices.get(e.getKey()), e.getValue()+"" );
			}
			if ( primaryKeys != null )
			{
				for ( Map.Entry e : primaryKeys.entrySet() )
				{
					ps.setString( valueIndices.size()+primaryIndices.get(e.getKey()), e.getValue()+"" );
				}
			}
			ps.executeUpdate();
			ps.close();
		} catch ( java.sql.SQLException sqle ) {
			sqle.printStackTrace();
		}
	}
public void insertUpdateInDatabase ( String tableName , String [ ] columnNames , Object [ ] values ) { HashMap < Object , Object > valuesKeys = new HashMap < Object , Object > ( ) ; for ( int i = 0 ; i < values . length ; i ++ ) { valuesKeys . put ( columnNames [ i ] , values [ i ] ) ; } HashMap < String , Object > primaryKeys = null ; try { DatabaseMetaData meta = connection . getMetaData ( ) ; ResultSet rs = meta . getPrimaryKeys ( null , null , tableName ) ; while ( rs . next ( ) ) { if ( primaryKeys == null ) primaryKeys = new HashMap < String , Object > ( ) ; String columnName = rs . getString ( "COLUMN_NAME" ) ; primaryKeys . put ( columnName , valuesKeys . get ( columnName ) ) ; valuesKeys . remove ( columnName ) ; } } catch ( SQLException sqle ) { sqle . printStackTrace ( ) ; } String cols = "" ; String patt = "" ; HashMap < Object , Integer > valueIndices = new HashMap < Object , Integer > ( ) ; int i = 1 ; for ( Map . Entry e : valuesKeys . entrySet ( ) ) { cols += ( i > 1 ? " , " : "" ) + e . getKey ( ) ; patt += ( i > 1 ? " , " : "" ) + "?" ; valueIndices . put ( e . getKey ( ) , i ) ; i ++ ; } String sql = null , opts = null ; HashMap < Object , Integer > primaryIndices = null ; if ( primaryKeys == null || primaryKeys . size ( ) == 0 ) { sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )" ; } else { primaryIndices = new HashMap < Object , Integer > ( ) ; opts = " WHERE " ; int p = 1 ; for ( Map . Entry e : primaryKeys . entrySet ( ) ) { opts += ( p > 1 ? " , " : "" ) + e . getKey ( ) + " = ? " ; primaryIndices . put ( e . getKey ( ) , p ) ; p ++ ; } String sqlFind = "SELECT * FROM " + tableName + " " + opts ; try { PreparedStatement psFind = connection . prepareStatement ( sqlFind ) ; for ( Map . Entry e : primaryKeys . entrySet ( ) ) { psFind . setString ( primaryIndices . get ( e . getKey ( ) ) , e . getValue ( ) . toString ( ) ) ; } result = psFind . executeQuery ( ) ; boolean found = next ( ) ; psFind . close ( ) ; if ( ! found ) { if ( DEBUG ) System . out . println ( String . format ( "No entry with %s found in table '%s'" , primaryKeys . toString ( ) , tableName ) ) ; int k = 1 , m = valueIndices . size ( ) ; for ( Map . Entry e : primaryKeys . entrySet ( ) ) { cols += ( m > 0 ? " , " : "" ) + e . getKey ( ) ; patt += ( m > 0 ? " , " : "" ) + "?" ; valuesKeys . put ( e . getKey ( ) , e . getValue ( ) ) ; valueIndices . put ( e . getKey ( ) , m + k ) ; k ++ ; } sql = "INSERT INTO " + tableName + " ( " + cols + " ) VALUES ( " + patt + " )" ; primaryKeys = null ; } else { cols = "" ; for ( Map . Entry e : valuesKeys . entrySet ( ) ) { cols += ( cols . equals ( "" ) ? "" : " , " ) + e . getKey ( ) + " = " + "?" ; } sql = "UPDATE " + tableName + " SET " + cols + " " + opts ; } } catch ( java . sql . SQLException sqle ) { sqle . printStackTrace ( ) ; } } if ( DEBUG ) System . out . println ( sql ) ; try { PreparedStatement ps = connection . prepareStatement ( sql ) ; for ( Map . Entry e : valuesKeys . entrySet ( ) ) { ps . setString ( valueIndices . get ( e . getKey ( ) ) , e . getValue ( ) + "" ) ; } if ( primaryKeys != null ) { for ( Map . Entry e : primaryKeys . entrySet ( ) ) { ps . setString ( valueIndices . size ( ) + primaryIndices . get ( e . getKey ( ) ) , e . getValue ( ) + "" ) ; } } ps . executeUpdate ( ) ; ps . close ( ) ; } catch ( java . sql . SQLException sqle ) { sqle . printStackTrace ( ) ; } }
Insert or update a bunch of values in the database.
Insert or update a bunch of values in the database .
//System.out.println(valuesKeys); //System.out.println(primaryKeys); //System.out.println( opts ); //System.out.println( sqlFind );